Re: [GENERAL] Sum of columns
On Mon, Sep 9, 2013 at 8:12 AM, Marc Mamin m.ma...@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: http://www.postgresql.org/message-id/CA+=1U=U-=oqv6p24pp7hro3dvaf3mh-oeltt7+f7rwzsc1x...@mail.gmail.com http://www.postgresql.org/message-id/1377525567350-5768573.p...@n5.nabble.com
Re: [GENERAL] Sum of columns
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-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of jane...@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
[GENERAL] Sum of columns
Hi, this is my query: SELECT user, sum(CASE WHEN lev = 50 AND lev 70THEN 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 = 90THEN 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 doesnt work like this. Does anyone know a solution Janek Sendrowski
Re: [GENERAL] 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, jane...@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
Re: [GENERAL] Sum of columns
On 9 Září 2013, 3:12, jane...@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
Re: [GENERAL] Sum of columns
On 09/08/2013 07:12 PM, jane...@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)
