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

Question

我在 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，抱歉我忘了输入那些...

Answer 1

如果 suburb，我只能想象您的查询会出现该错误是其中一张表中的一列。解决此问题的一种方法是在 from 中定义值条款:

select v.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 cross join lateral
     (values (coalesce(a.city, b.location))) as v(suburb)
where a.store_name ilike 'McDonalds'
group by v.suburb;

这是在 group by 中允许列别名的缺点之一。 .有时，您可能会与表列发生冲突。