sorting query results

[tom_b]

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

[Tweek]

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:

ID    Name   Number
1     bob       20
2     jim       30
3     bill      15
4     bob       20
5     jim       35
6     bill      15
 

then you could do a query something like this:

SELECT Name, Sum(Number) AS NumberTotal
FROM week
GROUP BY Name
ORDER BY Name DESC;
 

you'll get exactly what ya want


Do ya follow? tell us how ya go :)

Tweek

[Tweek]

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

SELECT Name, Sum(Number) AS NumberTotal
FROM week
GROUP BY Name
ORDER BY Sum(Number) DESC;
 

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

[tom_b]

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

[tom_b]

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

SELECT Name, Sum(Number) AS NumberTotal
FROM week
GROUP BY Name
ORDER BY Sum(Number) DESC;
 

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

[tom_b]

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?

[Tweek]

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):

ID    Name   Number   WeekNumber
1     bob       20      1
2     jim       30      1
3     bill      15      1
4     bob       20      2
5     jim       35      2
6     bill      15      2
 

Then i could lookup any week when ever I wanted like:

SELECT * FROM week WHERE WeekNumber = <Some Number>;

A query like:

SELECT * FROM week
WHERE WeekNumber = (SELECT Max(WeekNumber) FROM week);
 

would give the latest week for each person:

4     bob       20      2
5     jim       35      2
6     bill      15      2
 

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