SQL Group By multiple categories

Question

This is the query I'm using to count how many events are booked for each category of bookings.

SELECT
  Categories.name,
  count(case when locations.name ='loc 1' then 1 end) as Location1,
  count(case when locations.name ='loc 2' then 1 end) as Location2,
  count(case when locations.name ='loc 3' then 1 end) as Location3,
  count(Categories.name) as total

FROM
  ...

group by Categories.name
with rollup

This gives me the following result:

+------------------------------------------------------------+
|Category_name   | Location1 | Location2 | Location3 | Total |
+------------------------------------------------------------+
|Cat1            | 1         | 2         | 2         | 5     | 
|Cat2            | 2         | 1         | 2         | 5     | 
|Cat3            | 2         | 2         | 2         | 6     | 
|Cat3(Extra)     | 1         | 3         | 2         | 6     | 
|Cat4            | 3         | 1         | 2         | 6     | 
+------------------------------------------------------------+
|Total per loc   | 9         | 9         | 10        | 28    |
+------------------------------------------------------------+

This was satisfactory until now! Now I need to do the same request but I think I need to change the group by (??). Is there a way to change my request to this :

Count for Cat1 the number of events per location.
Count for Cat2 the number of events per location.
Count for Cat3 and Cat3(extra) the number of events per location.
Count for Cat4 the number of events per location.

What I mean is some Categories need to be counted together and what I'm looking to achieve is this result :

+------------------------------------------------------------+
|Category_name   | Location1 | Location2 | Location3 | Total |
+------------------------------------------------------------+
|Cat1            | 1         | 2         | 2         | 5     | 
|Cat2            | 2         | 1         | 2         | 5     | 
|Cat3            | 3         | 5         | 4         | 12    | 
|Cat4            | 3         | 1         | 2         | 6     | 
+------------------------------------------------------------+
|Total per loc   | 9         | 9         | 10        | 28    |
+------------------------------------------------------------+

Not optimum but one way to do it would be to group by first 4 characters of category name rather than the complete name. Something like this: http://stackoverflow.com/questions/666525/group-by-first-character — Maximus2012, Jun 22 '15 at 13:49
Thanks. I preferred the use of a statement (CASE WHEN Categories.name like 'Cat3%' THEN 'Cat3' ELSE Categories.name END) to avoid possible error when categories name are too similar. Thanks a lot for your input. — s_b, Jun 23 '15 at 11:14
Sure. Another approach (which I think might have better performance and functionality) could be creating an integer column for category_group, assign `Cat3` and `Cat3(Extra)` to same category_group and then do a GROUP BY category_group. Not sure how much table modifications you are allowed to make in your case though. — Maximus2012, Jun 23 '15 at 13:38

score 2 · Accepted Answer · answered Jun 22 '15 at 13:51

Just include a case statement for the group by expression:

  SELECT (CASE WHEN Categories.name like 'Cat3%' THEN 'Cat3'
               ELSE Categories.name
          END) as name,
         sum(locations.name = 'loc 1' ) as Location1,
         sum(locations.name = 'loc 2') as Location2,
         sum(locations.name = 'loc 3') as Location3,
         count(*) as total
  FROM ...
  GROUP BY (CASE WHEN Categories.name like 'Cat3%' THEN 'Cat3'
                 ELSE Categories.name
            END)

score 0 · Answer 2 · answered Jun 22 '15 at 13:49

0

sure, just add change the group definition to combine the new names into a single 'group' ...

SELECT
   substring(Categories.name, 1, 4) CategoryName,
   count(case when locations.name ='loc 1' then 1 end) Location1,
   count(case when locations.name ='loc 2' then 1 end) Location2,
   count(case when locations.name ='loc 3' then 1 end) Location3,
   count(Categories.name) total,

FROM
  ...

group by substring(Categories.name, 1, 4)
with rollup

answered Jun 22 '15 at 13:49

Charles Bretana

143,358
22
150
216

Thanks Charles. I preferred the use of a statement (CASE WHEN Categories.name like 'Cat3%' THEN 'Cat3' ELSE Categories.name END) to avoid possible error when categories name are too similar. Thanks a lot for your input. – s_b Jun 23 '15 at 11:12

SQL Group By multiple categories

2 Answers2