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!
'Complex' MySQL Query
Moderator: General Moderators
Re: 'Complex' MySQL Query
You could try SUM() and GROUP BY. 
Re: 'Complex' MySQL Query
Looks perfect, thanks a lotjackpf wrote:You could try SUM() and GROUP BY.
Re: 'Complex' MySQL Query
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
I just want:
NAME - TheSUM
Help is great appreciated, thanks
Re: 'Complex' MySQL Query
Like
[sql]SELECT SUM(`Columnname`) AS `someColumn`[/sql]
[sql]SELECT SUM(`Columnname`) AS `someColumn`[/sql]
Re: 'Complex' MySQL Query
So I sort of create a temporary column name for it?jackpf wrote:Like
[sql]SELECT SUM(`Columnname`) AS `someColumn`[/sql]
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
You tell me 
(yes, in theory)
(yes, in theory)
-
mybikeisgreen
- Forum Newbie
- Posts: 22
- Joined: Thu Oct 01, 2009 6:22 pm
Re: 'Complex' MySQL Query
You need to add the name to the selectoscardog wrote:So I sort of create a temporary column name for it?jackpf wrote:Like
[sql]SELECT SUM(`Columnname`) AS `someColumn`[/sql]
So the full query would be
[sql]SELECT SUM(`score`) AS `scoreSum` FROM playerstats GROUP BY name;[/sql]
Would that work?
[sql]SELECT `name`, SUM(`score`) AS `scoreSum` FROM playerstats GROUP BY name;[/sql]