Re: Sum of columns

On 9 Září 2013, 3:12, janek12(at)web(dot)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