Sum of Count

[BoarderLine]

Hi.  Is it possible to get the total sum of all count values returned in a query (not total rows of the query but the total sum of a count), but have it returned as a separate variable within the query?.

 

If by using the following query:

SELECT COUNT(a.field) AS number FROM a WHERE a.field='1' 

 

it returns:

6
7
9

 

Is it possible to somehow place a variable within the query that will give the value:

22

from the above example?

[requinix]

How are you getting multiple rows in the resultset? There should only be one: the number of rows in the table that have a.field=1.

[BoarderLine]

Of course, sorry.  What I meant to put as example query was this:

SELECT COUNT(IF(a.field='1',1,NULL)) AS number, b.name FROM b LEFT JOIN a ON a.id = b.id 

 

example result:

henry    6
fred     7
harry    9  
terry    0

[silkfire]

Just do:

 

SELECT SUM(number)

[BoarderLine]

I can get the result I need with:-

SELECT COUNT(IF(a.field='1',1,NULL)) AS number, b.name FROM b LEFT JOIN a ON a.id = b.id GROUP BY b.name WITH ROLLUP 

 

Which results:-

henry    6
fred     7
harry    9  
terry    0
NULL     22

 

I can then get the ROLLUP value by using mysql_data_seek() however this makes the rest of the dataset unusable later on the page.  I think I may have to move this now to the php forum, unless anyone can share a work around??  Thanks.

[BoarderLine]

And sorry forgot the essentials: MySQL 4.1.47

[silkfire]

Why don't you just sum it with PHP?

[BoarderLine]

Thanks silkfire,  The problem is I am looping through the data set in a list but need the total sum of 'counter' for the whole data set before the result list is populated.  I am trying to avoid doing this in two queries to reduce processing time. 

[silkfire]

Easy then. Loop thru the result set and then save each value into one array.

Then use array_sum to print the sum and loop thru the array to print each individual's name and score.

[BoarderLine]

Ta mate.  Saved me. Cheers 

[BoarderLine]

And obviously this way the WITH ROLLUP was not needed.