PostgreSQL 9.6 using case-when clause based on sum of the column

Question

Here is create and insert script:

CREATE TABLE numbers (
  id integer NOT NULL,
  number1 integer NOT NULL
);

INSERT INTO numbers (id, number1) VALUES
  ('1', '1'),
  ('2', '1'),
  ('1', '2'),
  ('1', '3');

if the sum of number1 column is even return its average, other wise return 0. So it should return [id:1, number1:0] for above example

I have tried this:

select id,
  case
    when sum(number1) %2=1 then 0 else avg(number1)
  end as number1
from numbers

it works fine with mySql but not for PostgreSQL (I used http://sqlfiddle.com to try it out). error message in PostgreSQL is

 ERROR: column "numbers.id" must appear in the GROUP BY clause or be used in an aggregate function

Everytime you SELECT a column while using aggregate functions, this column must be in the GROUP BY list. This is what the error is telling you. — Marcelo Zabani
– Marcelo Zabani, Commented Jul 16, 2017 at 15:14

klin · Accepted Answer · 2017-07-16 15:14:10Z

1

Why the expected value of id is 1 and not 2? Is this defined by the query in any way? MySql does not care about it, in contrary to Postgres (and other well known RDBMs).

If the value of id is negligible:

select
    case
        when sum(number1) %2 = 1 then 0 
        else avg(number1)
    end as number1
from numbers;

 number1 
---------
       0
(1 row)

If you want to get e.g. the smallest value of id:

select
    min(id) as id,
    case
        when sum(number1) %2 = 1 then 0 
        else avg(number1)
    end as number1
from numbers;

 id | number1 
----+---------
  1 |       0
(1 row)

answered Jul 16, 2017 at 15:14

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aryan Firouzian Over a year ago

Thanks for the answer, the id was an unimportant row and as you said it caused the problem.

Collectives™ on Stack Overflow

PostgreSQL 9.6 using case-when clause based on sum of the column

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related