PHP/SQL GroupBy *and* sum total of Grouped Figure

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

simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

PHP/SQL GroupBy *and* sum total of Grouped Figure

Post by simonmlewis »

Code: Select all

$result = mysql_query ("
SELECT * FROM matterusersrights 
INNER JOIN matterdata ON matterdata.clientcode = matterusersrights.clientcode 
WHERE matterusersrights.userid = '" .$cookieid."' GROUP BY persondealing");
 
while ($row = mysql_fetch_object($result)) {
        echo "<br/>
<b> $row->persondealing</b>
  $row->estimate<br/>
  $row->total<br/>
 
";
}       
    mysql_free_result($result);
    mysql_close($sqlconn);
Hi all - I really seem to come up with problems that seem impossible!

The above code is trying to extract various bits of data, but mainly the name from the field "persondealing" of which there could be several of the same person, and a total of the "total" field.

For instance:
Joe Bloggs, £2,500
Billy Bob, £1000
Joe Bloggs, £1,500

I want to see as a result:
Joe Bloggs, £4,000
Billy Bob, £1,000.

I can do a groupby, but that doesn't total up the amount.

Can some genius help?

Simon
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: PHP/SQL GroupBy *and* sum total of Grouped Figure

Post by VladSun »

simonmlewis wrote:The above code is trying to extract various bits of data, but mainly the name from the field "persondealing" of which there could be several of the same person, and a total of the "total" field.
You must GROUP BY something which is really unique - e.g. ID field in your persons table. There is a huge chance for having two persons with the same names ;)
simonmlewis wrote:For instance:
Joe Bloggs, £2,500
Billy Bob, £1000
Joe Bloggs, £1,500

I want to see as a result:
Joe Bloggs, £4,000
Billy Bob, £1,000.

I can do a groupby, but that doesn't total up the amount.

Can some genius help?

Simon
[sql]SELECT *, sum(money)[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: PHP/SQL GroupBy *and* sum total of Grouped Figure

Post by simonmlewis »

I don't think you understood the question.

The whole point of this *IS* for people with the same name. That's why I listed the results as I would want them.

So if Joe Bloggs had two entries, it would group him as one, but add up both his Total columns.

It's a list to show the total amount being charged by each person.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: PHP/SQL GroupBy *and* sum total of Grouped Figure

Post by VladSun »

I think I do understand the issue, but you don't.

You have two different persons:

John Smith, age 34, address: WA... etc.
John Smith, age 45, address: Alaska ... etc.

show me your results again ;)
There are 10 types of people in this world, those who understand binary and those who don't
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: PHP/SQL GroupBy *and* sum total of Grouped Figure

Post by simonmlewis »

Apologies - I get you.
That's impossible, because I know the source of the people's names. There are no two people with the same name.

I can see your concern, but I won't worry about that just now.

Simon
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: PHP/SQL GroupBy *and* sum total of Grouped Figure

Post by VladSun »

So .. did you try the "sum(money)" ?
There are 10 types of people in this world, those who understand binary and those who don't
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: PHP/SQL GroupBy *and* sum total of Grouped Figure

Post by simonmlewis »

How would i plant that into the SQL code, and then render it in the echo?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: PHP/SQL GroupBy *and* sum total of Grouped Figure

Post by VladSun »

I can't tell you that - I don't know what's your DB design.
You just need to add SUM(field_to sum) in you SELECT clause.
To make it easier to reference it use alias:
[sql]SUM(field_to sum) AS amount_per_person[/sql]
and you'll be able to echo it by using:

Code: Select all

echo $row['amount_per_person'];
I see you work with real persons and real money data - so it's very important that your code contains no errors. So, I would advice you to read some SQL manuals - http://www.w3schools.com/sql/default.asp
There are 10 types of people in this world, those who understand binary and those who don't
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: PHP/SQL GroupBy *and* sum total of Grouped Figure

Post by simonmlewis »

Code: Select all

$result = mysql_query ("
SELECT * FROM matterusersrights 
INNER JOIN matterdata ON matterdata.clientcode = matterusersrights.clientcode 
WHERE matterusersrights.userid = '" .$cookieid."' GROUP BY persondealing, SUM(total) AS amount_per_person");
 
while ($row = mysql_fetch_object($result)) {
        echo "<br/>
<b> $row->persondealing</b>
  $row->estimate<br/>
  $row->total<br/>";
echo  $row['amount_per_person'];
}       
    mysql_free_result($result);
    mysql_close($sqlconn);
This fails with a 'fetch_object' error, and I think it's because of the "SUM" section as it's on that line.
I have to select various columns, not just SUM.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: PHP/SQL GroupBy *and* sum total of Grouped Figure

Post by Mark Baker »

You're trying to select the sum, not group by the sum

Code: Select all

SELECT persondealing, 
       SUM(total) AS amount_per_person 
  FROM matterusersrights 
 INNER JOIN matterdata ON matterdata.clientcode = matterusersrights.clientcode 
 WHERE matterusersrights.userid = '" .$cookieid."' 
 GROUP BY persondealing
 
And you'll need to include every selected column (other than your sum) in the GROUP BY expression
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: PHP/SQL GroupBy *and* sum total of Grouped Figure

Post by simonmlewis »

Code: Select all

$result = mysql_query ("
SELECT persondealing, estimate, SUM(total) AS amount_per_person FROM matterusersrights 
INNER JOIN matterdata ON matterdata.clientcode = matterusersrights.clientcode 
WHERE matterusersrights.userid = '" .$cookieid."' GROUP BY persondealing, estimate");
 
while ($row = mysql_fetch_object($result)) {
        echo "<br/>
<b> $row->persondealing</b>
  $row->estimate<br/>
echo $row['amount_per_person'];
}       
    mysql_free_result($result);
    mysql_close($sqlconn);
It's not liking this at all:
Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING
It's on the

Code: Select all

echo $row['amount_per_person'];
row.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: PHP/SQL GroupBy *and* sum total of Grouped Figure

Post by Mark Baker »

You're using mysql_fetch_object, so:


Advice reinserted:

When using mysql_fetch_assoc():
$row['amount_per_person'];

When using mysql_fetch_object():
$row->amount_per_person
Last edited by Mark Baker on Mon Jan 19, 2009 7:34 am, edited 3 times in total.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: PHP/SQL GroupBy *and* sum total of Grouped Figure

Post by VladSun »

Review your code and you'll find out where your error is.

Hint: mysql_fetch_assoc(), mysql_fetch_object()

EDIT:
@Mark Baker
Let him think for a while, please.
There are 10 types of people in this world, those who understand binary and those who don't
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: PHP/SQL GroupBy *and* sum total of Grouped Figure

Post by simonmlewis »

Put rather sarcastically .... both the last comments help completely - not!

I have been doing nothing but look for the answer.

Brick walls are painful things.... when struck.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: PHP/SQL GroupBy *and* sum total of Grouped Figure

Post by Eran »

I actually thought they were very helpful. Did you take the time to go over your code at the point of the error?
Post Reply