Page 1 of 1

Query Help

Posted: Tue Mar 25, 2008 5:29 am
by shiznatix
I have this more advanced query (for me) that I need help with. The basic problem is mixing DISTINCT with SUM in the select.

There are about 90,000 rows that need to be used to get what I need. Each row has a gross_rake and room_username. There are probably 500 distinct room_username's for the whole 90,000 rows. For each distinct room_username I want to get the sum of all the gross_rake's. So if I have the room_username's TESTER, BILLY, and JOLE and 10 rows for each name, I want to return the distinct room_username plus that users total gross_rake.

This is the basic query that doesn't work but gets my point across.

Code: Select all

 
SELECT
    DISTINCT room_username,
    SUM(gross_rake)
FROM
    rb_raketracking
WHERE
    fk_room_id = "5"
AND
    data_date LIKE "2008-03%"
 
I have been doing all these calculations in PHP but when you select 90,000 rows and start organizing them into smaller chunks you run out of memory fast. Breaking the query up into 10,000 row blocks works around the memory problems but it takes twice as long to finish running so thats out as well.

In conclusion, hope you know how to do this because I don't.

Re: Query Help

Posted: Tue Mar 25, 2008 2:55 pm
by Christopher
Use GROUP BY instead of DISTINCT. They are almost identical. A general rule is to use DISTINCT with column names and GROUP BY when using functions. But that is simplifying it. There is a set of functions, SUM is one of them, that are associated with GROUP BY. See the manual.

Code: Select all

 
SELECT
    SUM(gross_rake)
FROM
    rb_raketracking
WHERE
    fk_room_id = "5"
AND
    data_date LIKE "2008-03%"
GROUP BY
    room_username