How to use count,'like' and group by in single query?

Question

I need something like

SELECT sum(case when name LIKE 'nick-1%' then 1 end) as Count,
       name 
FROM dummytable 
GROUP BY CASE 
           WHEN name LIKE 'nick-1%' THEN 'nick' 
           WHEN name LIKE 'vicky-1%' THEN 'vicky' 
           ELSE NULL 
         END

Name  Count
nick    10
vicky   17

nick means it should contain name like 'nick%' vicky means it should contain name like 'vicky%'

In my query I cant able to view name column since using sum it ask me to add it to group by but I cant able to add it to group by since I am using like function for it

Answer 1

To resolve the problem you are having, you only need to repeat the same CASE expression you currently have in your GROUP BY in your SELECT as well:

SELECT CASE WHEN name LIKE 'nick-1%'  THEN 'nick'
            WHEN name LIKE 'vicky-1%' THEN 'vicky'
            ELSE NULL END AS Name,
       COUNT(*) AS Count
FROM dummytable
GROUP BY CASE WHEN name LIKE 'nick-1%'  THEN 'nick'
              WHEN name LIKE 'vicky-1%' THEN 'vicky'
              ELSE NULL END

In your original query, you were mixing two types of aggregation. If you want to use conditional aggregation over the entire table, which you original SELECT was trying to do, you can use the following query:

SELECT 'nick' AS Name, SUM(CASE WHEN name LIKE 'nick-1%' THEN 1 ELSE 0 END) AS Count
FROM dummytable
UNION ALL
SELECT 'vicky', SUM(CASE WHEN name LIKE 'vicky-1%' THEN 1 ELSE 0 END)
FROM dummytable