CODEWITHSUNDEEP
sql
Ace Bayless22
Ace Bayless22

Reputation: 1

How to group when you already grouped

I'm lost at a query. I am trying to query the number of festivals based off of tickets sold. IE: The number of festivals hosted that sold between 0 and 250 tickets (Small event), # of festivals hosted that sold between 251 and 500 tickets (Medium event)

What I want is something like this:

Total Festivals Festival Size
30 Small Festival
50 Medium Festival
75 Large Festival

I tried something like this, but it never split out what I was looking to achieve:

Select count(*) as Total_Festivals, size.festival_type as Festival_Size
from
(Select count(distinct ticket.id) as number_festivals,
case when (Count purchase.id) between '0' and '250' as small_festival
case when (Count purchase.id) between '251' and '500' as medium_festival
case when (Count purchase.id) between '501' and '750' as large_festival
case when (Count purchase.id) > 750 as XL_Festival end as festival_type
from ticket
join purchase on ticket.type = purchase.type) as Size
group by 2

Upvotes: 0

Views: 62

Answers (1)

Willi Jakob
Willi Jakob

Reputation: 29

You can use CTEs to build separate datasets and work with them:

WITH cte AS(
   SELECT purchase.type, COUNT(*) Countpurchase
   FROM purchase
   GROUP BY  purchase.type   
), cte2 AS (
      SELECT
      cte.Countpurchase,  
      CASE WHEN cte.Countpurchase <= 250 THEN 'Small Festival'
           WHEN cte.Countpurchase <= 500 THEN 'Medium Festival'
           WHEN cte.Countpurchase <= 750 THEN 'Large Festival'
           ELSE 'XL Festival'
      END 'Festival Size',
      FROM ticket
      INNER JOIN cte
      ON cte.type = ticket.type
   )

SELECT SUM(cte2.Countpurchase) 'Total Festivals',
cte2.[Festival Size]
FROM cte2
GROUP BY cte2.[Festival Size]

Upvotes: 1

Related Questions