sorting sum with field_name

[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 with the corresponding sum sorted desc like this

 

bob    65

jim      40

bill      30

 

I can get the sums, I can get the field names, but I can't get them into separate rows and sort them by the sums.  I've tried a number of things but I'm stumped!!!  Any ideas, hints?

 

Thanks, Tom

[magnetica]

Have you tried the ORDER BY

 

Example:

 

ORDER BY column DESC

OR

ORDER BY column ASC

 

On the end of a select statement

[Jessica]

Can you show us the existing query?

[tom_b]

Here's the query I have to get the sums. 

 

$query = 'SELECT SUM(jim) AS jim, SUM(bob) AS bob, SUM(bill) AS bill FROM week';

$result = mysql_query($query) or die(mysql_error());

 

I use this to get the field names:

 

$values['jim]['name'] = mysql_field_name($result, 0);

$values['bob']['name'] = mysql_field_name($result, 1);

$values['bill']['name'] = mysql_field_name($result, 2);

 

I can get the sums O.K. and can output them with their field names, but I can't get the sums and their corresponding field names into rows sorted descending.  Maybe different queries?  An array I don't know how to use?  Thanks for taking time.

[Jessica]

...FROM week ORDER BY jim, bob, bill';

[tom_b]

Tried it, even tried 'order by' in a different order, no effect.

[utexas_pjm]

You can simplify your life by changing up your table structure a bit.  Consider the following:

 

counts

id    userid  count   

1      1        20   

1      2        30   

1      3        15   

2      1        20   

2      2        35   

2      3        15   

 

users

userid  username

1          jim

2          bob

3          bill

 

Now you can use the query:

 

SELECT users.name, SUM(counts.count) as totalCount 
FROM users INNER JOIN counts 
USING (userid) 
GROUP BY users.userid 
ORDER BY totalCount DESC

 

Best,

 

Patrick

[tom_b]

Hi!  Thank you for the idea!!  I'm new to this and this is a new idea to me but it seems to make a lot of sense, something I hadn't considered before.  In fact it might help with some other issues I've had.  I'll give it a try and let you know!!

 

Thanks, Tom