$15
How to sum grouped columns?

I ran this query:

SELECT count(id) as total FROM `question`

WHERE (status='active' or status='completed') 

GROUP BY user_id 

HAVING total > 1

ORDER BY total desc

to get the results we reported on our blog. The questions faded away with this pattern:

20
10
8
8
5
5
4
4
4
3
3
3
3
3
3
3
3
3
3
3
3
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2

This may be a dumb question, but how do I sum these?

I tried to simply take out the 'group by' clause:

SELECT count(id) as total FROM `question`

WHERE (status='active' or status='completed') 

HAVING total > 1

ORDER BY total desc

But then I get '209' as the answer, which is the total of all questions. It is as if the the HAVING clause is ignored.

So how do I get the sum?

Lawrence Krubner | 05/30/10 at 10:36pm | Edit

having, having clause, sums

(2) Possible Answers Submitted...

Last edited:
05/31/10
12:43pm
Lew Ayotte says:
You're trying to sum the count(id) as total, right?
- 05/30/10 11:22pm
  
  Lew Ayotte says:
  If so, try this:
```
SELECT sum(total) as sum 

FROM (SELECT count(id) as total FROM `question`

         WHERE (status='active' or status='completed') 

         HAVING total > 1

         ) as t
```
- 05/31/10 12:38pm
  
  Lawrence Krubner says:
  Great. You forgot the GROUP BY clause but I added that back in and it worked:
  
  SELECT sum(total) as sum
  
  FROM (SELECT count(id) as total FROM `question`
  
  WHERE (status='active' or status='completed')
  
  GROUP BY user_id
  
  HAVING total > 1
  
  ) as t
Last edited:
05/31/10
12:43pm
Marius Rugan says:
Say hello to the wizard:

http://dev.mysql.com/tech-resources/articles/wizard/index.html
practically it's the same stuff as described in the article - cross tabulation

SELECT location, SUM(IF(status='active',1,0)) AS active, SUM(IF(status='completed',1,0)) AS completed FROM question GROUP BY user_id;
- 05/31/10 12:43pm
  
  Lawrence Krubner says:
  Marius, that article is eye-opening. I had no idea I could stuff like that with MySql.