Query Help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Query Help

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Query Help

Post 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
 
(#10850)
Post Reply