sql - Postgres : column must appear in the GROUP BY clause or be used in an aggregate function

Question

我有一个查询，它对列进行 SUM 聚合并按原样选择名为 year 的列，
但我收到一个错误 -

SQL Error [42803]: ERROR: column "snapshot.year" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 211

以下是我的查询-

select 
'2019-09-11' as snapshot_date, 
SUM(case when snapshot_date = '2019-09-11' then balance end) as opening_balance,
SUM(case when snapshot_date = '2019-09-09' then balance end) as closing_balance,
year 
from snapshot

DDL-

CREATE TABLE snapshot (
    id bigserial NOT NULL,
    user_id int8 NOT NULL,
    latest_transaction_id int8 NOT NULL,
    wor_balance numeric(15,2) NOT NULL DEFAULT 0.00,
    last_transaction_timestamp int8 NOT NULL,
    last_transaction_date timestamp NOT NULL,
    snapshot_date date NOT NULL,
    "year" int2 NOT NULL,
    CONSTRAINT wor_snapshot_pkey PRIMARY KEY (id)
);

Answer 1

如错误提示，需要在FROM后添加GROUP BY子句:所以查询应该是这样的:

select 
'2019-09-11' as snapshot_date, 
SUM(case when snapshot_date = '2019-09-11' then balance end) as opening_balance,
SUM(case when snapshot_date = '2019-09-09' then balance end) as closing_balance,
year 
from snapshot
group by year

参见:https://www.javatpoint.com/postgresql-group-by-clause