[SOLVED] sum & count limit

[poe]

i have :

$db->getOneRow(" SELECT count(*) as cnt, sum(rf) FROM mlb_teamgbg WHERE stc = 22 ORDER BY datestamp DESC LIMIT 5 ");

 

but the count and sum seem to be calculating on the entire db, not just the 5 entries i have a limit of 5 on?

[fenway]

You're limiting the number of rows returned, NOT the number of rows considered by the count... if you want the latter, you'll need to derive a table without the aggregate functions, and then use SUM/COUNT on that (untested):

 

SELECT count(*) as cnt, sum(rf) FROM 
( SELECT rf FROM mlb_teamgbg WHERE stc = 22 ORDER BY datestamp DESC LIMIT 5 ) AS sub

[GingerRobot]

Beat me to it fenway, but thats the query i had too:

 

SELECT COUNT(*) as cnt,sum(rf) FROM (SELECT rf FROM mlb_teamgbg WHERE stc= 22 ORDER BY datestamp DESC LIMIT 5) as subquery

 

 

[poe]

awsome, thanx guys.