Jump to content

sorting sum with field_name


tom_b

Recommended Posts

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

Link to comment
https://forums.phpfreaks.com/topic/40101-sorting-sum-with-field_name/
Share on other sites

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.

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

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.