时间戳

首页 > 解决方案 > 在 Postgres 中使用 COALESCE 并按结果值分组

sql - 在 Postgres 中使用 COALESCE 并按结果值分组

问题描述

我在 Postgres 数据库中有两个表：

表一

transaction_id | city   | store_name | amount
-------------------------------
123            | London | McDonalds  | 6.20
999            | NULL   | KFC        | 8.40
etc...

表b

transaction_id | location | store_name | amount
-----------------------------------
123            | NULL     | McDonalds  | 6.20
999            | Sydney   | KFC        | 7.60
etc...

如您所见，该位置可能在一个表中丢失但出现在另一个表中。例如，对于事务 123，该位置存在于表 a 中，但在表 b 中缺失。除此之外，其余数据（amount等store_name）是相同的，逐行假设我们加入transaction_id.

对于给定的商家，我需要检索位置列表和该位置的总金额。

所需结果的示例：

KFC sales Report:

suburb   | suburb_total
---------------
London   | 2500
Sydney   | 3500

我尝试了什么：

select 
  coalesce(a.city, b.location) as suburb, 
  sum(a.amount) as suburbTotal
from tablea a
join tableb b on a.transaction_id = b.transaction_id
where a.store_name ilike 'KFC'
group by(suburb);

但我得到了错误column "a.city" must appear in the GROUP BY clause or be used in an aggregate function

所以我尝试了：

select 
  coalesce(a.city, b.location) as suburb, 
  sum(a.amount) as suburbTotal,
  max(a.city) as city_max,
  max(b.location) as location_max
from tablea a
join tableb b on a.transaction_id = b.transaction_id
where a.store_name ilike 'McDonalds'
group by(suburb);

但是，令人惊讶的是，我遇到了同样的错误，即使我现在正在聚合函数中使用该列。

我怎样才能达到预期的结果？

注意，我们在两个表中对数据进行非规范化是有原因的，这目前不在我的控制范围内。我必须处理它。

编辑：添加了 FROM 和 JOIN，抱歉我忘了输入这些...

标签： sqlpostgresqljoingroup-by

解决方案

您的查询缺少一个from子句，这使得您不清楚您正在尝试实现哪种逻辑。

根据您的样本数据和预期结果，我认为这是一个full joinon the transaction_id，然后是聚合。在子句中使用位置参数group by可避免重复表达式：

select 
    store_name,
    coalesce(a.city, b.location) as suburb, 
    sum(amount) suburb_total
from tablea a
full join tableb b using(transaction_id)
group by 1, 2

推荐阅读