SQL;只计算每列中指定的值 [英] SQL; Only count the values specified in each column

问题描述

在SQL中，我有一个名为answer的列，值可以是1或2.我需要生成一个SQL查询，计算每个月的1和2的数量。我有以下查询，但它不工作：

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

推荐答案

尝试SUM-CASE技巧：

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

这篇关于SQL;只计算每列中指定的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！