[SOLVED] Count # of rows from MYSQL Query Result

[bsamson]

I wasn't sure how to word the subject ... but here's what I am trying to do.

 

I have a mysql table that looks something like this:

 

StoreNumber | Date | Score1 | Score2 | Score3

 

Now, as an example I have this in this table ...

 

01 | 05/09/2008 | 50 | 20 | 30

04 | 05/06/2008 | 28 | 12 | 45

07 | 05/01/2008 | 85 | 43 | 76

04 | 05/04/2008 | 21 | 11 | 18

07 | 05/09/2008 | 54 | 78 | 10

02 | 05/02/2008 | 12 | 43 | 30

 

Now, let's say I run this query:

SELECT SUM(score1)+SUM(score2)+SUM(score3) as totalscore FROM v2breakdown WHERE DATE_FORMAT(FROM_UNIXTIME(`entrydate`), '%Y/%m/%d') >= '2008/05/01' ORDER BY totalscore ASC

 

 

Obviously one of the things I know is the results will be listed low to high which in turn will reveal where that location ranks. I am just trying to determine where a location ranks company wide. The above query will list them in order but outside of an if..then statement is there something I can add to the query that will tell me the store's rank?

 

Thanks in adance!

 

 

[rarebit]

You'll want to use a group COUNT function within your query.

[GingerRobot]

You could use a subquery:

 

SELECT score1+score2+score3 as totalscore,(SELECT COUNT(*)+1 FROM v2breakdown WHERE totalscore < score1+score2+score3) as position FROM v2breakdown WHERE DATE_FORMAT(FROM_UNIXTIME(`entrydate`), '%Y/%m/%d') >= '2008/05/01' ORDER BY totalscore ASC

 

 

[bsamson]

Thank you GingerRobot!!!!! WORKS GREAT!