如何基于tbl_test表按Code分组统计不同Remarks的金额总和？

现有数据表tbl_test结构及数据如下：

Code     |amount |Date   |Mos   |Remarks 
R1        300     1/7/25  Jul    Collect
R2        150     1/7/25  Jul    collect
R3        150     1/7/25  Jul    release    
R2        200     2/7/25  Jul    release
R3        200     3/7/25  Jul    release
R3        200     3/7/25  Jul    Cashout
R1        200     4/7/25  Jul    CashOut

需求为按Code分组，分别统计Remarks为collect、release、cashout的amount总和（需兼容Remarks大小写不一致的情况），预期输出：

CODE    |ttlCollect     |ttlrelease     |ttlcashout
R1      |300            |0              |200
R2      |150            |200            |0
R3      |0              |450            |200 

通用SQL语法（支持多数数据库） #

使用CASE WHEN实现条件统计：

SELECT 
    Code AS CODE,
    SUM(CASE WHEN LOWER(Remarks) = 'collect' THEN amount ELSE 0 END) AS ttlCollect,
    SUM(CASE WHEN LOWER(Remarks) = 'release' THEN amount ELSE 0 END) AS ttlrelease,
    SUM(CASE WHEN LOWER(Remarks) = 'cashout' THEN amount ELSE 0 END) AS ttlcashout
FROM tbl_test
GROUP BY Code
ORDER BY Code;

MySQL专属简化写法 #

使用SUM(IF())实现相同逻辑：

SELECT 
    Code AS CODE,
    SUM(IF(LOWER(Remarks) = 'collect', amount, 0)) AS ttlCollect,
    SUM(IF(LOWER(Remarks) = 'release', amount, 0)) AS ttlrelease,
    SUM(IF(LOWER(Remarks) = 'cashout', amount, 0)) AS ttlcashout
FROM tbl_test
GROUP BY Code
ORDER BY Code;

关键说明 #

• LOWER(Remarks)将备注统一转换为小写，避免大小写差异导致统计遗漏（如Collect和collect会被归为同一类）
• GROUP BY Code确保每个Code仅返回一行统计结果
• 条件判断中用ELSE 0保证无对应类型时显示0而非NULL

内容的提问来源于stack exchange，提问作者Dhan Bacongan