CODEWITHSUNDEEP
mysql
Ekaterina
Ekaterina

Reputation: 534

Using sum function with when case

SELECT  '2017-05-30' AS `short_date`,
        'Stats' AS `group`,
        'Active ' AS `subgroup`,
         '' AS `row`,
         CASE
WHEN COUNT(DISTINCT(guild_name)) >= 1 AND  COUNT(DISTINCT(guild_name)) <= 5   THEN '1 - 5 ' 
WHEN COUNT(DISTINCT(guild_name)) >= 6 AND COUNT(DISTINCT(guild_name)) <= 10  THEN '6 - 10 '
WHEN COUNT(DISTINCT(guild_name)) >= 11 AND COUNT(DISTINCT(guild_name)) <= 15 THEN '11 - 15 '
WHEN COUNT(DISTINCT(guild_name)) >= 16 AND COUNT(DISTINCT(guild_name)) <= 20 THEN '16 - 20 '
WHEN COUNT(DISTINCT(guild_name)) >= 21 AND COUNT(DISTINCT(guild_name)) <= 25 THEN '21 - 25 '
WHEN COUNT(DISTINCT(guild_name)) >= 30 THEN '> 30' END AS `value`
FROM  table
WHERE guild_name !=0
GROUP BY
                `short_date`,
                `group`,
                `subgroup`

How not to count a value in each case, how to count it once and use in each case?

Upvotes: 0

Views: 35

Answers (1)

Osuwariboy
Osuwariboy

Reputation: 1375

How about you move the count to a subquery and move the case outside like this:

select `short_date`, `group`, `subgroup`, `row`,   
CASE
    WHEN nb_guild >= 1 AND nb_guild <= 5   THEN '1 - 5 ' 
    WHEN nb_guild >= 6 AND nb_guild <= 10  THEN '6 - 10 '
    WHEN nb_guild >= 11 AND nb_guild <= 15 THEN '11 - 15 '
    WHEN nb_guild >= 16 AND nb_guild <= 20 THEN '16 - 20 '
    WHEN nb_guild >= 21 AND nb_guild <= 25 THEN '21 - 25 '
    WHEN nb_guild >= 30 THEN '> 30' 
END AS `value`
from 
(
    SELECT  '2017-05-30' AS `short_date`,
            'Stats' AS `group`,
            'Active ' AS `subgroup`,
            '' AS `row`,
            COUNT(DISTINCT(guild_name)) as nb_guild
    FROM  table
    WHERE guild_name !=0
    GROUP BY
                    `short_date`,
                    `group`,
                    `subgroup`
) as subquery

This way your count only gets executed once in the subquery and your outer query does its treatment on the resulting resultset.

Upvotes: 3

Related Questions