Page 1 of 1

'Complex' MySQL Query

Posted: Fri Oct 02, 2009 11:31 am
by oscardog
This might be simple, haha and i'm being silly anyway.

I have loads of entries in a database, they're batsmen/bowler stats, but for now only batsmen.

Each player will have a unique name, so there might be "Joe Bloggs" and then "Joe Bloggs(2)" but say we use Joe Bloggs as an example. I want to get all the data in the database, group it per name. So say there is 4 entries for Joe Bloggs, 2 for Ashley Lloyd, 3 for Harry Dickens I want to group them to each name and then add up the scores entered.

I'm not explaining this very well am I? But basically there will be lots of entries per player, but I want the get the scores from each entry and ad them up so the db would look like:

Joe Blogs - 14
Joe Blogs - 19
Ashley Lloyd - 30
Joe Blogs - 10
Harry Dickens - 5
Harry Dickens - 10
Ashley LLoyd - 5

Then it would output as:
Joe Blogs - 14 + 19 + 10 (So it would echo 43)
Harry Dickens - 5 + 10 (So display 15)
Ashley Lloyd - 30 + 5 (So display 35).

Get it now? I hope so :/

I think it will need to loop, but I just have no idea how to group them together :/

Thanks!

Re: 'Complex' MySQL Query

Posted: Fri Oct 02, 2009 11:36 am
by jackpf
You could try SUM() and GROUP BY. :)

Re: 'Complex' MySQL Query

Posted: Fri Oct 02, 2009 11:43 am
by oscardog
jackpf wrote:You could try SUM() and GROUP BY. :)
Looks perfect, thanks a lot :)

Re: 'Complex' MySQL Query

Posted: Fri Oct 02, 2009 12:01 pm
by oscardog
I do have one question. How do I output the data? Normally it would be $row['columnname']; but it clearly wont work this time as there is no 'sum' column.

I just want:
NAME - TheSUM

Help is great appreciated, thanks :)

Re: 'Complex' MySQL Query

Posted: Fri Oct 02, 2009 12:08 pm
by jackpf
Like
[sql]SELECT SUM(`Columnname`) AS `someColumn`[/sql]

Re: 'Complex' MySQL Query

Posted: Fri Oct 02, 2009 12:27 pm
by oscardog
jackpf wrote:Like
[sql]SELECT SUM(`Columnname`) AS `someColumn`[/sql]
So I sort of create a temporary column name for it?

So the full query would be

[sql]SELECT SUM(`score`) AS `scoreSum` FROM playerstats GROUP BY name;[/sql]

Would that work?

Re: 'Complex' MySQL Query

Posted: Fri Oct 02, 2009 1:47 pm
by jackpf
You tell me :P

(yes, in theory)

Re: 'Complex' MySQL Query

Posted: Fri Oct 02, 2009 1:54 pm
by mybikeisgreen
oscardog wrote:
jackpf wrote:Like
[sql]SELECT SUM(`Columnname`) AS `someColumn`[/sql]
So I sort of create a temporary column name for it?

So the full query would be

[sql]SELECT SUM(`score`) AS `scoreSum` FROM playerstats GROUP BY name;[/sql]

Would that work?
You need to add the name to the select

[sql]SELECT `name`, SUM(`score`) AS `scoreSum` FROM playerstats GROUP BY name;[/sql]