Sum of columns

does
sum (case when lev >= 50 then 1 else 0 end) as matches

do what you want?

On Sun, Sep 8, 2013 at 9:12 PM, <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 c,
sum(CASE WHEN 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

--
The person who says it cannot be done should not interrupt the person who
is doing it. -- Chinese Proverb

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&#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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 09/08/2013 07:12 PM, 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 c,
sum(CASE WHEN 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

How far does this get you?
insert into t_temp_fts values('rob', 51), ('rob', 71), ('rob', 81),
('rob', 91);
insert into t_temp_fts values('jon', 51), ('jon', 71), ('jon', 81),
('jon', 91);
SELECT distinct usern,
(select count(*) from t_temp_fts i where o.usern = i.usern and
lev >= 50 AND lev < 70) as a,
(select count(*) from t_temp_fts i where o.usern = i.usern and
lev >= 70 AND lev < 80)as b ,
(select count(*) from t_temp_fts i where o.usern = i.usern and
lev >= 80 AND lev < 90)as c ,
(select count(*) from t_temp_fts i where o.usern = i.usern and
lev > 90) as d
from t_temp_fts o
;
usern | a | b | c | d
-------+---+---+---+---
jon | 1 | 1 | 1 | 1
rob | 1 | 1 | 1 | 1
(2 rows)

hi,

in addition to the others comments, you can also remove " ELSE 0 " from your query.
It will result in <NULL> values that are discarded by SUM.

regards,

Marc Mamin

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of janek12@web.de
Sent: Montag, 9. September 2013 03:13
To: pgsql-general@postgresql.org
Subject: [GENERAL] Sum of columns

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 c,
sum(CASE WHEN 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

On Mon, Sep 9, 2013 at 8:12 AM, Marc Mamin <M.Mamin@intershop.de> wrote:

hi,

in addition to the others comments, you can also remove " ELSE 0 " from
your query.****

It will result in <NULL> values that are discarded by SUM.****

For that matter, you could clean this up by using COUNT as your aggregate
with a GROUP BY. See these two archives:
/messages/by-id/CA+=1U=U-=OQv6P24PP7HrO3dvAf3mH-oELTT7+F7RWZsC1XkMQ@mail.gmail.com
/messages/by-id/1377525567350-5768573.post@n5.nabble.com