SQL 查询在一个查询中对同一 ID 列的两个不同值求和 [英] SQL query to sum two different values of same ID column in one query

问题描述

好心，有人可以帮我解决这个问题，我有下表:

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

这篇关于SQL 查询在一个查询中对同一 ID 列的两个不同值求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！