mysql - 同一查询中来自同一表的 2 个不同值并按日期分组

Question

现在表中的输出对于总香蕉是正确的，但对于绿色香蕉，输出是每天的总产量。我希望它将每天的香蕉总数和绿色香蕉总数分组。

SELECT bananas, date,

(SELECT COUNT(bananas) 
FROM banana_table
WHERE bananas = 'green')
AS green_bananas,

COUNT(bananas) AS total_bananas

FROM banana_table

GROUP BY date

标签： mysqlsql

Answer 1

使用条件聚合：

SELECT bananas, date,
       SUM( bananas = 'green' ) as green_bananas,
       COUNT(bananas) AS total_bananas
FROM banana_table
GROUP BY date;