SQL 查询在一个查询中对同一 ID 列的两个不同值求和
问题描述：
好心，有人可以帮我解决这个问题，我有下表:
kindly, can someone help me with this, I have the following table:
表名:余额
cname amount type
--------------------------------------
Jhon 150 A
Jhon 200 B
Jhon 100 A
Jhon 30 A
Jhon 55 B
========================================
======================================
我想要一个 SQLquery 给我这个结果:
I want an SQLquery gives me this result:
cname totalOf_A totalOf_B
---------------------------------------
Jhon 280 255
=========================================
我已经尝试了以下:
select ,cname,sum(amount),type from balances group by cname,type
结果:
cname amount type
---- ----- ----
Jhon 250 A
Jhon 255 B
所以，我希望结果在一行和一个查询中.
请提出任何建议.
提前致谢.
so, I want the result in one row and in one query please.
Any suggestions please.
Thanks in advance.
答
您要做的就是所谓的条件聚合.你可以使用
What you're trying to do is called conditional aggregation. You can use
select
cname,
sum(case when type='A' then amount else 0 end) as total_A,
sum(case when type='B' then amount else 0 end) as total_B
from balances
group by cname