How to write a query to produce counts for arbitrary value bands?

My table had 3 fields: id and unit. I want to count how many ids have <10, 10-49, 50-100 etc units. The final result should look like:

Category | countIds  
<10      | 1516  
10 - 49  | 710  
50 - 99  | 632  
etc.
开发者_如何学Python

This is the query that returns each id and how many units it has:

select id, count(unit) as numUnits
from myTable
group by id

How can I build on that query to give me the category, countIds result?

create temporary table ranges (
  seq         int primary key,
  range_label varchar(10),
  lower       int,
  upper       int
);

insert into ranges values
(1, '<10',     0, 9), 
(2, '10 - 49', 10, 49),  
(3, '50 - 99', 50, 99)
etc.

select r.range_label, count(c.numUnits) as countIds
from ranges as r 
join (
  select id, count(unit) as numUnits
  from myTable
  group by id) as c
on c.numUnits between r.lower and r.upper
group by r.range_label
order by r.seq;

edit: changed sum() to count() above.

select category_bucket, count(*) 
  from (select case when category < 10 then "<10"
                    when category >= 10 and category <= 49 then "10 - 49"
                    when category >= 50 and category <= 99 then "50 - 99"
                    else "100+"
               end category_bucket, num_units
          from my_table)
  group by category_bucket 

A dynamically grouped solution is much harder.

SELECT id, countIds
FROM (
SELECT id
     , 'LESS_THAN_TEN' CATEGORY
     , COUNT(unit) countIds
  FROM table1
 GROUP BY ID
 HAVING COUNT(UNIT) < 10
UNION ALL
SELECT id
     , 'BETWEEN_10_AND_49' category
     , COUNT(unit) countIds
  FROM table1
 GROUP BY ID
 HAVING COUNT(UNIT) BETWEEN 10 AND 49
UNION ALL
SELECT id
     , 'BETWEEN_50_AND_99' category
     , COUNT(unit) countIds
  FROM table1
 GROUP BY id
 HAVING COUNT(UNIT) BETWEEN 50 AND 99
) x

Giving an example for one range: (10 - 49)

select count(id) from 
(select id, count(unit) as numUnits from myTable group by id) 
where numUnits >= '10' && numUnits <= '49' 

It's not precisely what you want, but you could use fixed ranges, like so:

select ' < ' || floor(id / 50) * 50, count(unit) as numUnits
  from myTable
group by floor(id / 50) * 50
order by 1

Try this working sample in SQL Server TSQL

SET NOCOUNT ON
GO
WITH MyTable AS
(
SELECT 00 as Id, 1 Value UNION ALL
SELECT 05 , 2 UNION ALL
SELECT 10 , 3 UNION ALL
SELECT 15 , 1 UNION ALL
SELECT 20 , 2 UNION ALL
SELECT 25 , 3 UNION ALL
SELECT 30 , 1 UNION ALL
SELECT 35 , 2 UNION ALL
SELECT 40 , 3 UNION ALL
SELECT 45 , 1 UNION ALL
SELECT 40 , 3 UNION ALL
SELECT 45 , 1 UNION ALL
SELECT 50 , 3 UNION ALL
SELECT 55 , 1 UNION ALL
SELECT 60 , 3 UNION ALL
SELECT 65 , 1 UNION ALL
SELECT 70 , 3 UNION ALL
SELECT 75 , 1 UNION ALL
SELECT 80 , 3 UNION ALL
SELECT 85 , 1 UNION ALL
SELECT 90 , 3 UNION ALL
SELECT 95 , 1 UNION ALL
SELECT 100 , 3 UNION ALL
SELECT 105 , 1 UNION ALL
SELECT 110 , 3 UNION ALL
SELECT 115 , 1 Value
)
SELECT Category, COUNT (*) CountIds
FROM 
(
    SELECT
        CASE 
            WHEN Id BETWEEN 0 and 9 then '<10' 
            WHEN Id BETWEEN 10 and 49   then '10-49' 
            WHEN Id BETWEEN 50 and 99   then '50-99' 
            WHEN Id > 99                then '>99' 
        ELSE '0' END as Category    
    FROM MyTable
) as A
GROUP BY Category

This will give you the following result

Category CountIds
-------- -----------
<10      2
>99      4
10-49    10
50-99    10