Re: Sum of columns - Mailing list pgsql-general

On 9 Září 2013, 3:12, janek12@web.de wrote:
> Hi,      this is my query:  SELECT user,          sum(CASE WHEN lev >= 50
> AND lev < 70 THEN 1 ELSE 0 END) as a,
>          sum(CASE WHEN lev >= 70 AND lev < 80 THEN 1 ELSE 0 END) as b,
>          sum(CASE WHEN lev >= 80 AND lev  90 THEN 1 ELSE 0 END) as d,
>          (SELECT a + b + a + d) AS matches
>          FROM t_temp_fts
>          GROUP BY user'     I like to add up the 4 columns a,b,c and d
> of every user, but it doesn't work like this.  Does anyone
> know a solution     Janek Sendrowski

Hi,

it doesn't work like that - the inner select makes no sense for various
reasons. I'd bet what you want is something this:

SELECT user, a, b, d, (a + b + d) AS matches
FROM (

          SELECT user,
          sum(CASE WHEN lev >= 50 AND lev < 70 THEN 1 ELSE 0 END) as a,
          sum(CASE WHEN lev >= 70 AND lev < 80 THEN 1 ELSE 0 END) as b,
          sum(CASE WHEN lev >= 80 AND lev < 90 THEN 1 ELSE 0 END) as d,
          FROM t_temp_fts
          GROUP BY user
) foo

i.e. it takes the t_temp_fts table, computes the partial results and then
passes the results to the outer query to evaluate the addition.

There's an alternative doing all of that in a single query:

SELECT user, a, b, d, (a + b + d) AS matches
FROM (

          SELECT user,
          sum(CASE WHEN lev >= 50 AND lev < 70 THEN 1 ELSE 0 END) as a,
          sum(CASE WHEN lev >= 70 AND lev < 80 THEN 1 ELSE 0 END) as b,
          sum(CASE WHEN lev >= 80 AND lev < 90 THEN 1 ELSE 0 END) as d,
          sum(CASE WHEN lev >= 50 AND lev < 70 THEN 1 ELSE 0 END) +
          sum(CASE WHEN lev >= 70 AND lev < 80 THEN 1 ELSE 0 END) +
          sum(CASE WHEN lev >= 80 AND lev < 90 THEN 1 ELSE 0 END) as matches,
          FROM t_temp_fts
          GROUP BY user
) foo

or you could add directly the CASE statements like this:

SELECT user, a, b, d, (a + b + d) AS matches
FROM (

          SELECT user,
          sum(CASE WHEN lev >= 50 AND lev < 70 THEN 1 ELSE 0 END) as a,
          sum(CASE WHEN lev >= 70 AND lev < 80 THEN 1 ELSE 0 END) as b,
          sum(CASE WHEN lev >= 80 AND lev < 90 THEN 1 ELSE 0 END) as d,
          sum((CASE WHEN lev >= 50 AND lev < 70 THEN 1 ELSE 0 END) +
              (CASE WHEN lev >= 70 AND lev < 80 THEN 1 ELSE 0 END) +
              (CASE WHEN lev >= 80 AND lev < 90 THEN 1 ELSE 0 END)) as
matches,
          FROM t_temp_fts
          GROUP BY user
) foo

All of this should return return the same results.

Tomas