'Complex' MySQL Query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
oscardog
Forum Contributor
Posts: 245
Joined: Thu Oct 23, 2008 4:43 pm

'Complex' MySQL Query

Post 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!
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: 'Complex' MySQL Query

Post by jackpf »

You could try SUM() and GROUP BY. :)
oscardog
Forum Contributor
Posts: 245
Joined: Thu Oct 23, 2008 4:43 pm

Re: 'Complex' MySQL Query

Post by oscardog »

jackpf wrote:You could try SUM() and GROUP BY. :)
Looks perfect, thanks a lot :)
oscardog
Forum Contributor
Posts: 245
Joined: Thu Oct 23, 2008 4:43 pm

Re: 'Complex' MySQL Query

Post 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 :)
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: 'Complex' MySQL Query

Post by jackpf »

Like
[sql]SELECT SUM(`Columnname`) AS `someColumn`[/sql]
oscardog
Forum Contributor
Posts: 245
Joined: Thu Oct 23, 2008 4:43 pm

Re: 'Complex' MySQL Query

Post 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?
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: 'Complex' MySQL Query

Post by jackpf »

You tell me :P

(yes, in theory)
mybikeisgreen
Forum Newbie
Posts: 22
Joined: Thu Oct 01, 2009 6:22 pm

Re: 'Complex' MySQL Query

Post 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]
Post Reply