我需要在SQL表中计数总寄存器。
我有这样的状态:
SELECT
COUNT(CASE WHEN status = 'WAITING_MEASUREMENT' THEN 1 ELSE NULL END) as '1',
COUNT(CASE WHEN status = 'WAITING_WEIGHMENT' THEN 1 ELSE NULL END) as '2',
COUNT(CASE WHEN status = 'WAITING_DATA_ENTRY' THEN 1 ELSE NULL END) as '3',
COUNT(CASE WHEN status = 'WAITING_PICTURES' THEN 1 ELSE NULL END) as '4'
FROM product;
但是,当我运行查询时,我在所有列中都有相同的结果。查询有什么问题?
您应该总和而不是计数,也应该使用0而不是null,计数只会计数所有记录。
SELECT
SUM(CASE WHEN status = 'WAITING_MEASUREMENT' THEN 1 ELSE 0 END) as '1',
SUM(CASE WHEN status = 'WAITING_WEIGHMENT' THEN 1 ELSE 0 END) as '2',
SUM(CASE WHEN status = 'WAITING_DATA_ENTRY' THEN 1 ELSE 0 END) as '3',
SUM(CASE WHEN status = 'WAITING_PICTURES' THEN 1 ELSE 0 END) as '4'
FROM product;