Troubles with subqueries using union-CodePudding

I am using union so I can "merge" the data from 3 separate queries. My table has many columns but I have to use only title and ID.

My data:

ID Title 
1 'trainings'  
2 'trainings'
3 'workshops'
4 'workshops'
5 'workshops'
6 'qwerty'
7 'qwerty2'
8 'qwerty3'

What I want to do is show only the titles of Trainings, workshop and combine these other three's as "Others" and then count each one of them . Example:

Count Title
2     trainings
3     workshops
3     others

What I have tried:

select  Title, count(*) as Count From Events where Title like '%Training%' 
group by Title 
union 
select Title, count(*) as Count From Events where Title like '%Workshop%' 
group by Title
union 
select 'Others' as Title,count(*) as Count from Events e where e.Title not like '%workshop%' and e.Title not like '%training%'
Group by Title

But I don't get the desired result, indeed I take 1 as per "Other's" column:

Count Title
1     Others
2     Trainings
3     Workshops

But if I run only this code:

select 'Others' as Title,count(*) as Count from Events e where e.Title not like '%workshop%' and e.Title not like '%training%'
Group by Title

it will work all fine, the "error" is only when I combine with the two others.

Can you please help me with this?

CodePudding user response：

Why not just use a single query with aggregation?

SELECT COUNT(*) AS [Count],
       CASE WHEN Title LIKE '%Training%' THEN 'Trainings' 
            WHEN Title LIKE '%Workshop%' THEN 'Workshops'
            ELSE 'Other'
       END AS Title
FROM dbo.Events
GROUP BY CASE WHEN Title LIKE '%Training%' THEN 'Trainings' 
              WHEN Title LIKE '%Workshop%' THEN 'Workshops'
              ELSE 'Other'
         END;

If you prefer not to repeat the CASE expression, you can use a CTE:

WITH Titles AS(
    SELECT CASE WHEN Title LIKE '%Training%' THEN 'Trainings' 
                WHEN Title LIKE '%Workshop%' THEN 'Workshops'
                ELSE 'Other'
           END AS Title
    FROM dbo.Events)
SELECT COUNT(*) AS [Count],
       Title
FROM Titles
GROUP BY Title;

CodePudding user response：

You can use a case expression for aggregation:

select (case when title in ('Trainings', 'Workshops') then title
             else 'Others'
        end) as title,
       count(*)
from events e
group by (case when title in ('Trainings', 'Workshops') then title
               else 'Others'
          end);

If you don't want to repeat the case expression you can use a lateral join with apply:

select v.title, count(*)
from events e cross join
     (values (case when title in ('Trainings', 'Workshops')
                   then title else 'Others'
              end)
     ) v(title)
group by v.title;

EDIT:

If you need the LIKE you would just extend this (there are no examples in the question where this is necessary):

select v.title, count(*)
from events e cross join
     (values (case when title like '%trainings%' then 'Training'
                   when title like '%workshops%' then 'Workshop'
                   else 'Others'
              end)
     ) v(title)
group by v.title;

CodePudding user response：

SELECT COUNT(ID) AS Count, Title FROM (SELECT ID, CASE WHEN Title IN ('trainings', 'workshops') THEN Title ELSE 'others' END Title FROM Events) AS T GROUP BY Title

CodePudding user response：

Another Way

select * from (
select  count(case when Title like '%trainings%' then 1 end) as trainings,
  count(case when Title like '%workshops%' then 1 end) as workshops,
  count(case when Title not like '%workshops%' and Title not like '%trainings%' then 1 end) as Other from Events
  )tab
  UNPIVOT 
( 
Counts FOR Title IN (trainings, workshops,Other)
) 
AS UnpivotTable order by Counts