sql - 在一列上使用 CASE 语句并计算另一列的聚合

Question

我正在尝试在一列上使用 case 语句，并在满足条件的情况下计算另一列的聚合。

我正在查询的示例数据表(例如酒店)如下所示:

|  date  | customer_name | customer_id | expense_type | billed_amount |
|  08-02 |   John Doe    |      1      |   personal   |     120.00    |
|  08-03 |   John Doe    |      1      |   personal   |      80.00    |
|  08-04 |   John Doe    |      1      |   corporate  |     205.00    |
|  08-02 |   Adam Smith  |      2      |   corporate  |     400.00    |
|  08-03 |   Adam Smith  |      2      |   personal   |     300.00    |
|  08-06 |   Adam Smith  |      2      |   corporate  |     150.00    |

下面是我写的SQL查询:

Select
    customer_name
    , customer_id
    , case when expense_type = 'personal' then sum(billed_amount) else 0 end as personal_bill
    , case when expense_type = 'corporate' then sum(billed_amount) else 0 end as corporate_bill
From payments
Where date > '08-01'
Group by 1, 2

我得到的错误信息是:

Column "expense_type" must appear in the GROUP BY clause or be used in an aggregate function

当我尝试对第 3 列(以及第 1 和第 2 列)进行分组时，我收到此错误消息:

Aggregates not allowed in GROUP BY clause

最后，下面说明了所需的结果表:

| customer name | customer_id | personal_bill | corporate_bill |
|   John Doe    |      1      |     200.00    |     205.00     |
|   Adam Smith  |      2      |     300.00    |     550.00     |

我能想到的一个解决方案是创建两个不同的子查询来限制 Where 部分中的“expense_type”(即 where expense_type = 'personal')，然后在主查询中查询它们，但这是很多类似的代码一线之差。你能帮我高效地写这个查询吗？谢谢你!

Answer 1

使用过滤器:

select
    customer_name,
    customer_id,
    sum(billed_amount) filter (where expense_type = 'personal') as personal_bill,
    sum(billed_amount) filter (where expense_type = 'corporate') as corporate_bill
from payments
where date > '08-01'
group by 1, 2

 customer_name | customer_id | personal_bill | corporate_bill 
---------------+-------------+---------------+----------------
 Adam Smith    |           2 |        300.00 |         550.00
 John Doe      |           1 |        200.00 |         205.00
(2 rows)