CODEWITHSUNDEEP
sqlsql-servergroup-by
Rouzbeh Zarandi
Rouzbeh Zarandi

Reputation: 1082

Query with nested GROUP BY

I want to apply two different angle of query in one statement. I have table of customers with information of city and number of products they bought as

    c_id,city,no_products
    1, city1,0
    2, city2,20
    3, city2,80
    4,city1,10

The summary I am looking for is grouped by city as well as grouped by no_products range including 0 counts for range which is not existed in table. Ranges are predefined as grpA = no_products between 0 to 50,grpB = no_products between 51 to 100.

city,total_customers,grpA,grpB
city1,2,2,0
city2,2,1,1

So as above I want to nested the select City,count(*) as city_customer_count from C_tbl group by City

with 

select t.range as [GROUPS], count(*) as [number of customers]
from (
  select case  
    when no_products between 0 and 20 then N'GROUPC'
    when no_products between 21 and 80 then N'GROUPB'
    when no_products > 80 then N'GROUPA'
     end as range
  from C_tbl) t
group by t.range

However the above query also not consider 0 counts for not existed range.

Upvotes: 0

Views: 158

Answers (1)

Nick
Nick

Reputation: 147166

You can use conditional aggregation for this:

SELECT city,
       COUNT(*) AS total_customers,
       COUNT(CASE WHEN no_products BETWEEN 0 AND 50 THEN 1 END) AS grpA,
       COUNT(CASE WHEN no_products BETWEEN 51 AND 100 THEN 1 END) AS grpB
FROM C_tbl
GROUP BY city

Output:

city    total_customers     grpA    grpB
city1   2                   2       0
city2   2                   1       1

Demo on SQLFiddle

Upvotes: 2

Related Questions