2

Based on this simple example table:

create table some_data (x integer, y integer);

insert into some_data (x,y) values
(1,1),
(1,2),
(1,1000),
(1,2),
(2,1),
(2,1),
(2,2);

I query it trying to sum the values based on particular conditions, like:

select
  case when x = 1 then
        sum(case when y = 1 then 1 else 0 end) as counter_x,
        sum(case when y = 2 then 2 else 0 end) as counter_y,
        sum(case when y > 2 then 7 else 0 end) as counter_z,
      end,

  sum(case when x = 1 then 1 else 0 end) as counter_a,
  sum(case when x = 2 and y = 1 then 10 else 0 end) as counter_b,
  sum(case when x = 3 and y = 30 then 400 else 0 end) as counter_c
  from some_data;

What I am expecting here is:

counter_x | counter_y | counter_z | counter_a | counter_b | counter_c
----------+-----------+-----------+-----------+-----------+----------
1         | 4         | 7         | 4         | 20        | 0

However my query doesn't work as the wrapping case when x = 1 is not allowed. How can I fix it?

  • What version of PostgreSQL? – Evan Carroll Jun 19 '17 at 23:02
  • Version postgres 9.5 – Randomize Jun 19 '17 at 23:03
  • cool, I've tried answer both sides of this question. You can't make the schema of the result dependent on the result itself. If that's what you're trying to do. See my answer, and consider clarifying if none of the answers here help you. – Evan Carroll Jun 19 '17 at 23:20
3

You should add a condition for every counter:

select
    sum(case when x = 1 and y = 1 then 1 else 0 end) as counter_x,
    sum(case when x = 1 and y = 2 then 2 else 0 end) as counter_y,
    sum(case when x = 1 and y > 2 then 7 else 0 end) as counter_z,
    sum(case when x = 1 then 1 else 0 end) as counter_a,
    sum(case when x = 2 and y = 1 then 10 else 0 end) as counter_b,
    sum(case when x = 3 and y = 30 then 400 else 0 end) as counter_c
from 
    some_data;

counter_x | counter_y | counter_z | counter_a | counter_b | counter_c
--------: | --------: | --------: | --------: | --------: | --------:
        1 |         4 |         7 |         4 |        20 |         0

dbfiddle here

  • The problem with that solution is case when x = 1 is redundant. It may be a redundant function for example (expensive to run). – Randomize Jun 17 '17 at 18:11
1

@McNets answer gives the simplest and probably most cost-effective SQL.

If you're worried about computing case when x = 1 (let's assume it is case f(x) = const) more times than needed, you can, in principle, precompute x = 1 values in a subquery:

SELECT
    sum(case when x1    and y = 1  then 1   end) as counter_x,
    sum(case when x1    and y = 2  then 2   end) as counter_y,
    sum(case when x1    and y > 2  then 7   end) as counter_z,
    sum(case when x1               then 1   end) as counter_a,
    sum(case when x = 2 and y = 1  then 10  end) as counter_b,
    sum(case when x = 3 and y = 30 then 400 end) as counter_c
FROM 
    (
    SELECT
        x, 
        (x = 1) AS x1, -- probably x = 1, or f(x) will only be computed once, but don't count on it
        y
    FROM 
         some_data
    ) AS pre_computed_values ;
counter_x | counter_y | counter_z | counter_a | counter_b | counter_c
--------: | --------: | --------: | --------: | --------: | --------:
        1 |         4 |         7 |         4 |        20 |      null

Whether the database query planner will plan this execution depends a lot on the cost associated which whatever the cost is for computing f(x), and wheter f(x) is IMMUTABLE or at least STABLE (see Function Volatility).

Also, when summing you can skip the else 0 clauses, if you can have NULL as a global response. The default value for a case when cond then v is to return NULL if cond is not met. And (most) aggregation functions, like SUM will simply ignore NULLs; returning NULL if all inputs are also NULLS. I don't think this saves significant (or any) computing time, but it saves some writing... You just can get you a small difference (counter_c).

dbfiddle here

0

However my query doesn't work as the wrapping case when x = 1 is not allowed. How can I fix it?

You can't make the query schema dependent on the result set.

  1. Those counts always have to be there regardless of what x equals.
  2. You have to use dynamic sql.

The problem with that solution is case when x = 1 is redundant. It may be a redundant function for example (expensive to run).

If that's so, if it is redundant then the query planner needs to be smart enough to handle it. You have limited control in a declarative language like SQL. Unfortunately there is no method to compose the aggregates manually like that though you could always create your own, it's not exactly easy. PostgreSQL will calculate these aggregates in a single table pass though.

Cleaning up the query

I also agree with McNets in that the CASE should be flattened. However, You can fold this together with count and an aggregate filter.

SELECT
    1   * count(*) FILTER (WHERE x=1 AND y=1)    AS counter_x,
    2   * count(*) FILTER (WHERE x=1 AND y=2)    AS counter_y,
    7   * count(*) FILTER (WHERE x=1 AND y>2)    AS counter_z,
    1   * count(*) FILTER (WHERE x=1)            AS counter_a,
    10  * count(*) FILTER (WHERE x=2 AND y=1)    AS counter_b,
    400 * count(*) FILTER (WHERE x=3 AND y=30)   AS counter_c
from 
    some_data;

Alternatively, you can use count without an aggregate filter..

400 * count(CASE WHEN x=3 AND y=30 THEN 1 END)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.