output sum, field_name sorted

[tom_b]

ok, I have a simple table in my DB, looks something like this:

 

id    jim    bob    bill

1      20      30      15

2      20      35      15

 

etc,  what i need to do is get the sums for each column (I can do that), then output each field_name and the corresponding sum sorted desc like this

 

bob    65

jim      40

bill      30

 

I've tried a number of things but I'm stumped!!!  Any ideas?

 

Thanks, Tom

[btherl]

You are trying to sort the columns?  SQL isn't designed for that.  Instead, you can do the sorting in php after fetching the data.

 

If you want to sort by name, then you should design your database like this:

 

score_id  name  score

1            jim      20

1            bob    30

1            bill      15

2            jim      20

2            bob    35

2            bill      15

 

Then just do

 

SELECT name, sum(score) FROM scores GROUP BY name ORDER BY sum(score) DESC

[tom_b]

Thanks, I'll give that a try.  I've been trying to do it with PHP, I can get the data O.K., just can't seem to get the field name and sum together in one row and then sort by the sum.  Maybe I can make it work this way, thanks for your time!!

 

Tom

[btherl]

If you use this query and mysql_fetch_assoc, then you can use asort():

 

SELECT sum(jim) as jim, sum(bob) as bob, sum(bill) as bill FROM tbl

 

Then fetch the result with mysql_fetch_assoc(), and use asort() or arsort() to sort

 

http://www.php.net/manual/en/function.arsort.php