分享
三行代码  ›  专栏  ›  技术社区  ›  Aniruddha Raje

Postgres:列必须出现在GROUP BY子句中或用于聚合函数中 - Postgres: column must appear in the GROUP BY clause or be used in an aggregate function

  •  0
  • Aniruddha Raje  · 技术社区  · 1 周前

    我有一个查询,它对列进行求和聚合,并选择名为 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)
    );
    

    enter image description here

    2 回复  |  直到 1 周前
        1
  •  3
  •   Dejan Vasic    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

        2
  •  2
  •   Phill    1 周前
    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
    

    您需要按年份分组以将数据聚合在一起进行汇总。