Page 1 of 1

Calculate total in table from query

Posted: Tue Apr 07, 2009 3:48 pm
by papa
Hi,

Code: Select all

 
$sql = "SELECT result_valueId, COUNT(result_valueId) AS total
         FROM ufc_result GROUP BY result_valueId";
 
Doing this query I get total results from different result values.

Array
(
[0] => Array
(
[result_valueId] => 1
[total] => 15
)

[1] => Array
(
[result_valueId] => 2
[total] => 10
)


etc

Is there a way to sum up the totals from the array in my SQL statement or do I need to do another query or use PHP?

thanks

Re: Calculate total in table from query

Posted: Tue Apr 07, 2009 7:49 pm
by Bill H
Check the MySQL SUM() function.

Re: Calculate total in table from query

Posted: Wed Apr 08, 2009 3:46 am
by papa
I'm aware of that function but not sure how to implement it to my query. Trying now I get weird results...

Code: Select all

        $sql = "SELECT COUNT(a.result_valueId) AS total, SUM(a.result_valueId) AS grand_total, b.rv_name
                FROM ufc_result AS a 
                JOIN ufc_result_value AS b ON a.result_valueId = b.result_valueId
                GROUP BY a.result_valueId";

Re: Calculate total in table from query

Posted: Wed Apr 08, 2009 7:33 am
by VladSun
Use a separate SUM query.
Your query returns multiple rows, while total sum is a single row result, so instead of adding a field with the same value (grand_total) to every row, you should use a separate query.

Re: Calculate total in table from query

Posted: Wed Apr 08, 2009 7:39 am
by papa
Ok thanks!