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' 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
