By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,949 Members | 1,855 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,949 IT Pros & Developers. It's quick & easy.

sorting query results

P: 18
Hi,

I'm using Mysql and PHP,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

here's the query to get the sums:
<?php
$query = 'SELECT SUM(jim) AS jim, SUM(bob) AS bob, SUM(bill) AS bill FROM week';
$result = mysql_query($query) or die(mysql_error());

and 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, 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
Feb 27 '07 #1
Share this Question
Share on Google+
6 Replies


P: 17
Hi,

I'm using Mysql and PHP,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

here's the query to get the sums:
<?php
$query = 'SELECT SUM(jim) AS jim, SUM(bob) AS bob, SUM(bill) AS bill FROM week';
$result = mysql_query($query) or die(mysql_error());

and 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, 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
Hi,

Im not sure how much experence you have with data bases but i think your problem may come from how you have used your table. Every time you add a new person you'll need to add a new field which is not normally how things are done (btw, i'm no database expert)

However if you set up your table like this:

Expand|Select|Wrap|Line Numbers
  1. ID    Name   Number
  2. 1     bob       20
  3. 2     jim       30
  4. 3     bill      15
  5. 4     bob       20
  6. 5     jim       35
  7. 6     bill      15
  8.  
then you could do a query something like this:

Expand|Select|Wrap|Line Numbers
  1. SELECT Name, Sum(Number) AS NumberTotal
  2. FROM week
  3. GROUP BY Name
  4. ORDER BY Name DESC;
  5.  

you'll get exactly what ya want


Do ya follow? tell us how ya go :)

Tweek
Feb 27 '07 #2

P: 17
Sorry i made a small mistake... we should order by number like:

Expand|Select|Wrap|Line Numbers
  1. SELECT Name, Sum(Number) AS NumberTotal
  2. FROM week
  3. GROUP BY Name
  4. ORDER BY Sum(Number) DESC;
  5.  
Just a hint, if you have MS Access installed you can setup a test database that looks the same as your MySQL one and try out some queries in there.

or you can get/buy frontends that work like Access for your MySQL databases which can be useful tools for developing and testing

Tweek
Feb 27 '07 #3

P: 18
Hi!! Well, once I get the database set up I wouldn't be adding anyone new to it. That's why the field names are people's names. I'd just be adding new numbers to each name, then wanting to output the sums with each field name. I've been thinking I could get each sum and corresponding field name into an array and sort it by sum but have had no luck!! But I might be able to make it work your way, I'll give it a try. Thanks for taking the time!!!

Tom
Feb 27 '07 #4

P: 18
Sorry i made a small mistake... we should order by number like:

Expand|Select|Wrap|Line Numbers
  1. SELECT Name, Sum(Number) AS NumberTotal
  2. FROM week
  3. GROUP BY Name
  4. ORDER BY Sum(Number) DESC;
  5.  
Just a hint, if you have MS Access installed you can setup a test database that looks the same as your MySQL one and try out some queries in there.

or you can get/buy frontends that work like Access for your MySQL databases which can be useful tools for developing and testing

Tweek
Well, I changed my database and tried your query, looks like it's going to work just fine for me!!! Thanks so much for you help!!!

Tom
Feb 27 '07 #5

P: 18
Well, I changed my database and tried your query, looks like it's going to work just fine for me!!! Thanks so much for you help!!!

Tom
If I could ask one more question, I've changed the database as you suggested and the above problem seems to be solved, but now I want to be able to get only the last entry for each name. I've tried all sorts of group by, order by, max(id) etc but with no effect, any ideas?
Feb 28 '07 #6

P: 17
Well, once I get the database set up I wouldn't be adding anyone new to it. That's why the field names are people's names.
like I said, I'm no database expert and there's probably a better way of doing it. In fact, in a big database, you'd probably have a entirly separate table for storing people


...I want to be able to get only the last entry for each name. I've tried all sorts of group by, order by, max(id) etc but with no effect, any ideas?
Well I can think of a few ways to do it, like you could get the last id of each person and store those values in an array and then look those up in another query.

However, I'd be temped to do something really simple (and I'm assuming this table stores number of hours worked per week or something like that) and add a field like "WeekNumber" to the table like (where WeekNumber is the week of the year):

Expand|Select|Wrap|Line Numbers
  1. ID    Name   Number   WeekNumber
  2. 1     bob       20      1
  3. 2     jim       30      1
  4. 3     bill      15      1
  5. 4     bob       20      2
  6. 5     jim       35      2
  7. 6     bill      15      2
  8.  
Then i could lookup any week when ever I wanted like:

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM week WHERE WeekNumber = <Some Number>;

A query like:

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM week
  2. WHERE WeekNumber = (SELECT Max(WeekNumber) FROM week);
  3.  
would give the latest week for each person:

Expand|Select|Wrap|Line Numbers
  1. 4     bob       20      2
  2. 5     jim       35      2
  3. 6     bill      15      2
  4.  
Im at work at the moment (so i can't really test PHP code here) but perhaps someone else could think of another way, I can't think of a way to do it entirely in a query with your current table

Tweek
Feb 28 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.