Page 1 of 2

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

Posted: Mon Jan 19, 2009 3:55 am
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

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

Posted: Mon Jan 19, 2009 4:00 am
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]

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

Posted: Mon Jan 19, 2009 4:14 am
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.

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

Posted: Mon Jan 19, 2009 4:19 am
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 ;)

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

Posted: Mon Jan 19, 2009 4:25 am
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

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

Posted: Mon Jan 19, 2009 4:27 am
by VladSun
So .. did you try the "sum(money)" ?

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

Posted: Mon Jan 19, 2009 4:33 am
by simonmlewis
How would i plant that into the SQL code, and then render it in the echo?

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

Posted: Mon Jan 19, 2009 5:03 am
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

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

Posted: Mon Jan 19, 2009 5:35 am
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.

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

Posted: Mon Jan 19, 2009 5:49 am
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

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

Posted: Mon Jan 19, 2009 6:17 am
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.

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

Posted: Mon Jan 19, 2009 6:41 am
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

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

Posted: Mon Jan 19, 2009 6:42 am
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.

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

Posted: Mon Jan 19, 2009 6:45 am
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.

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

Posted: Mon Jan 19, 2009 6:51 am
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?