tom_b Posted February 26, 2007 Share Posted February 26, 2007 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 More sharing options...
magnetica Posted February 26, 2007 Share Posted February 26, 2007 Have you tried the ORDER BY Example: ORDER BY column DESC OR ORDER BY column ASC On the end of a select statement Link to comment https://forums.phpfreaks.com/topic/40101-sorting-sum-with-field_name/#findComment-194015 Share on other sites More sharing options...
Jessica Posted February 26, 2007 Share Posted February 26, 2007 Can you show us the existing query? Link to comment https://forums.phpfreaks.com/topic/40101-sorting-sum-with-field_name/#findComment-194017 Share on other sites More sharing options...
tom_b Posted February 26, 2007 Author Share Posted February 26, 2007 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. Link to comment https://forums.phpfreaks.com/topic/40101-sorting-sum-with-field_name/#findComment-194019 Share on other sites More sharing options...
Jessica Posted February 26, 2007 Share Posted February 26, 2007 ...FROM week ORDER BY jim, bob, bill'; Link to comment https://forums.phpfreaks.com/topic/40101-sorting-sum-with-field_name/#findComment-194022 Share on other sites More sharing options...
tom_b Posted February 26, 2007 Author Share Posted February 26, 2007 Tried it, even tried 'order by' in a different order, no effect. Link to comment https://forums.phpfreaks.com/topic/40101-sorting-sum-with-field_name/#findComment-194024 Share on other sites More sharing options...
utexas_pjm Posted February 26, 2007 Share Posted February 26, 2007 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 Link to comment https://forums.phpfreaks.com/topic/40101-sorting-sum-with-field_name/#findComment-194027 Share on other sites More sharing options...
tom_b Posted February 26, 2007 Author Share Posted February 26, 2007 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 Link to comment https://forums.phpfreaks.com/topic/40101-sorting-sum-with-field_name/#findComment-194039 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.