CODEWITHSUNDEEP
sqlcount
Johnnny
Johnnny

Reputation:

SQL; Only count the values specified in each column

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`)

Upvotes: 3

Views: 2205

Answers (3)

Roee Adler
Roee Adler

Reputation: 33990

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

Upvotes: 6

Georg Schölly
Georg Schölly

Reputation: 126105

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

Upvotes: 0

Andomar
Andomar

Reputation: 238086

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`)

Upvotes: 5

Related Questions