How to do conditional aggregation in postgres?

Question

I want to do conditional aggregation in Postgres. I have a table with an id column and a float items column.

The query I want to write is something like this:

SELECT ( ((SUM(items) * 3) WHERE id="123"))
       + ((SUM(items) * 2) WHERE items="124")) )
FROM mytable

And I'd like to get a single number out. So for example, if my table looked like this:

org_id     items
123        10
123        3
124        12

I'd like to get back the single number 63 (i.e. 13*3 + 12*2).

I'm pretty sure I need a CASE statement, but I'm not sure how to implement it.

Quassnoi · Accepted Answer · 2016-04-15 16:29:50Z

8

In PostgreSQL 9.4+:

SELECT  SUM(items) FILTER (WHERE org_id = 123) * 3 +
        SUM(items) FILTER (WHERE org_id = 124) * 2
FROM    mytable

, or, in earlier versions,

SELECT  SUM(items * CASE org_id WHEN 123 THEN 3 WHEN 124 THEN 2 END)
FROM    mytable

However, if you have lots of pairs like that, it would make sense to store them in a table (rather than hardcoding) and just use this:

SELECT  SUM(items * coeff)
FROM    (
        VALUES
        (123, 3),
        (124, 2)
        ) m (id, coeff)
JOIN    mytable
USING   (id)

Replace the nested query m with your actual table

answered Apr 15, 2016 at 16:29

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

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

1 Answer 1