Query using a sum

I like the below code and it works

$financial = $SQL->query(“SELECT gross_price FROM donations WHERE id = ‘$id’”);
$money = $SQL->fetch_array($financial);

but when I make this sum(gross_price) it no longer works. What I am doing wrong. Thanks for any help

1 Like

That is a valid query. So you need to be more specific in regards to what you mean by “doesn’t work”. Is there an error or are you just not getting back the expected results? If the former post the error if the later what results do you expect.

1 Like

What is in the GROUP BY clause - the part that tells the statement what to include in each sum.

I wanted just 1 value to show that is the off all prices by id.

I agree with @oddz, the problem here is that you’re not telling us what you mean by “doesn’t work”. The only thing I can think of is that you’re maybe accessing the results as an associative array and having trouble with the column name. Try

$financial = $SQL->query("select sum(gross_price) as sumprice from donations where id = '$id'");
$money = $SQL->fetch_array($financial);
echo $money['sumprice'];

If that doesn’t help, give us more information exactly how things don’t work, error messages, etc.

You can also use “Group By” in your query to sum all values.

so you need

GROUP BY id

Might it be a simple as the gross_price field being the wrong datatype?

Unless the rows are being grouped the data type will not matter since the sum of a single value is always the value itself.

You might be vulnerable to sql injection attack, is the value for $id supplied by the user?

We’re all clutching at straws until @jodyschwarz helps us out with how it “doesn’t work”.

1 Like

Well the posted code doesn’t actually sum anything because it doesn’t identify any groups to sum them in.

When I asked what the groups are for the summing I was told that it is only supposed to be returning one result for the id specified - so what doesn’t work has been identified.

The fix is:

"SELECT sum(gross_price) FROM donations WHERE id = '$id' GROUP BY id"

Only that it would work without GROUP BY just fine, as long as he wants to get only 1 id
In that case, the solution is:
SELECT sum(gross_price) FROM donations WHERE id = '$id'
otherwise the solution is:
SELECT id, sum(gross_price) FROM donations GROUP BY id

maybe you want to use an alias

SELECT sum(gross_price) as total FROM donations WHERE id = '$id'

echo $money['total'];
1 Like

That was my assumption, it did seem strange that OP would need to group results by id when the original query limits the results to a single id, but I’m not an SQL expert by any means. Would GROUP BY still be required when there are multiple rows with the same id? The sample I looked at just now on a tutorial suggests not. I assume there must be multiple rows with the same ID, otherwise the SUM() is redundant.

That’s what I was driving at above, when I was talking about the column names in the associative array. But the OP might be accessing the result with the numeric index.

No. It is actually quite useless to group by columns that aren’t in the SELECT statement, because then you’d never know which result is related to which (in this case) id.

And you’ll typically get an error grouping on something not included in the SELECT. At least, I’ve always gotten an error, and ended up smacking myself upside the head for being an idiot…

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.