Query Help
Posted: Tue Mar 25, 2008 5:29 am
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.
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.
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%"
In conclusion, hope you know how to do this because I don't.