Count number of rows in particular bucket at state level

user_10535439 0
Viewed 54

I have a table as below. It gives information about user_id, his state and number of subscriptions as below

+---------+-------+---------------+
| user_id | State | Subscriptions |
+---------+-------+---------------+
|       1 | LA    |             4 |
|       2 | LA    |             4 |
|       3 | LA    |            12 |
|       4 | LA    |            12 |
|       5 | LA    |             8 |
|       6 | LA    |             3 |
|       7 | NY    |            14 |
|       8 | NY    |            15 |
|       9 | NY    |             3 |
|      10 | NY    |             2 |
|      11 | NY    |             4 |
|      12 | NY    |            12 |
|      13 | OH    |             6 |
|      14 | OH    |             8 |
|      15 | OH    |             2 |
|      16 | OH    |             3 |
+---------+-------+---------------+

I want to transform the data in a such a way that I want the number of records in each bucket at a state level as below

+--------------------+----+----+----+
| Subscription_Range | LA | NY | OH |
+--------------------+----+----+----+
| 1 to 4             |  3 |  3 |  2 |
| 5 to 11            |  1 |  0 |  2 |
| 12 to 15           |  2 |  3 |  0 |
+--------------------+----+----+----+

Any help will be appreciated.

SQL Fiddle

sql-server sql
1 Answers

You can use a case and conditional aggreation:

select (case when subscriptions <= 4 then '1 to 4'
             when subscriptions <= 11 then '5 to 11'
             when subscriptions <= 15 then '12 to 15'
        end) as subscription_range,
       sum(case when state = 'LA' then 1 else 0 end) as LA,
       sum(case when state = 'NY' then 1 else 0 end) as NY,
       sum(case when state = 'OH' then 1 else 0 end) as OH
from t
group by (case when subscriptions <= 4 then '1 to 4'
               when subscriptions <= 11 then '5 to 11'
               when subscriptions <= 15 then '12 to 15'
          end)
order by min(subscriptions);
user_11440350
Related