在单个查询中对多个列值求和和分组

Question

我希望从同一列中获取多个状态的摘要。

select c.brand
sum amount as total
from charges as c
where c.invoive_id is not null
and c.paid = true
group by c.brand

获取按品牌分组的所有已完成购买的总和。 我想在同一查询中有一个单独的列，按品牌汇总"c.paid = false" 所以我会有：

Brand     Total(true)    Total(false)
b_one         25             12
b_two         38             16

Answer 1

你似乎有一个简单的条件聚合语句 -

SELECT c.brand
,SUM(CASE WHEN c.paid = 'true' THEN amount END) as Total(true)
,SUM(CASE WHEN c.paid = 'false' THEN amount END) as Total(false)
from charges as c
where c.invoive_id is not null
group by c.brand

Answer 2

你没有说你正在使用哪个数据库，所以我假设是PostgreSQL。通常可以使用CASE子句来执行此操作。例如：

select 
c.brand,
sum(case when c.paid then 1 else 0 end) as total_true,
sum(case when c.paid then 0 else 1 end) as total_false
from charges as c
where c.invoive_id is not null
group by c.brand

Answer 3

在支持布尔类型的数据库中，您通常可以执行以下操作：

select c.brand,
sum(c.paid) as num_true,
sum(not c.paid) as num_falst
from charges as c
where c.invoive_id is not null
group by c.brand