SQL; Only count the values specified in each column

Question

In SQL I have a column called "answer", and the value can either be 1 or 2. I need to generate an SQL query which counts the number of 1's and 2's for each month. I have the following query, but it does not work:

SELECT MONTH(`date`), YEAR(`date`),COUNT(`answer`=1) as yes,
COUNT(`answer`=2) as nope,` COUNT(*) as total

FROM results

GROUP BY YEAR(`date`), MONTH(`date`)

Roee Adler · Answer 1 · 2009-05-27T12:04:33.490

6

I would group by the year, month, and in addition the answer itself. This will result in two lines per month: one counting the appearances for answer 1, and another for answer 2 (it's also generic for additional answer values)

SELECT MONTH(`date`), YEAR(`date`), answer, COUNT(*)
FROM results
GROUP BY YEAR(`date`), MONTH(`date`), answer

edited May 27 '09 at 12:04

answered May 27 '09 at 11:53

Roee Adler

33,434
32
105
133

Same idea as I had. I'm sure it's also faster. – Georg Schölly May 27 '09 at 11:59
Nice idea, though this would not return the total count – Andomar May 27 '09 at 12:15
@Andomar: from the question - "i need to generate an SQL query which counts the amount of one and twos for each month". He did not mention anything about counting the total (although it does appear in his query, I suspect it's just because it's "easy"). Counting the total is not a big challenge, simply remove the "answer" from the GROUP BY and from the SELECT. – Roee Adler May 27 '09 at 12:32

score 5 · Accepted Answer · answered May 27 '09 at 11:43

5

Try the SUM-CASE trick:

SELECT 
    MONTH(`date`), 
    YEAR(`date`),
    SUM(case when `answer` = 1 then 1 else 0 end) as yes,
    SUM(case when `answer` = 2 then 1 else 0 end) as nope,
    COUNT(*) as total
FROM results
GROUP BY YEAR(`date`), MONTH(`date`)

answered May 27 '09 at 11:43

Andomar

232,371
49
380
404

A variation of this is to use count(distinct case when `answer`=1 then `answer`else null end). It is more useful when you have joins that will make the same rows appear more than once and you have a distinct value in answer (using sum(1) will add it several times for the same master row). Not useful in this case, but worth mentioning :) – Jimmy Stenke May 27 '09 at 11:56

score 0 · Answer 3 · answered May 27 '09 at 11:58

SELECT year,
       month,
       answer
       COUNT(answer) AS quantity
FROM results
GROUP BY year, month, quantity

year|month|answer|quantity
2001|    1|     1|     2
2001|    1|     2|     1
2004|    1|     1|     2
2004|    1|     2|     2

SELECT * FROM results;

year|month|answer
2001|    1|     1
2001|    1|     1
2001|    1|     2
2004|    1|     1
2004|    1|     1
2004|    1|     2
2004|    1|     2

SQL; Only count the values specified in each column

3 Answers3

Linked