Search Postgresql Archives

Re: Sum of columns

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 9 Září 2013, 3:12, janek12@xxxxxx 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&#39;     I like to add up the 4 columns a,b,c and d
> of every user, but it doesn&#39;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



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux