Calculate total in table from query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Calculate total in table from query

Post 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
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: Calculate total in table from query

Post by Bill H »

Check the MySQL SUM() function.
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Calculate total in table from query

Post 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";
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Calculate total in table from query

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Calculate total in table from query

Post by papa »

Ok thanks!
Post Reply