SQL: help request

Question

There were 3 fields in my table: id and unit. I want to calculate how many identifiers have 10, 10-49, 50-100, etc. The end result should look like this:

Category | countIds <10 | 1516 10 - 49 | 710 50 - 99 | 632 etc. 

This is a query that returns each identifier and the number of units:

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

How can I build this query to give me a category, the result of countIds?

Note. I could not understand what to call this question. Feel free to edit it / offer suggestions

Answer 1

 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.

Answer 2

 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 more complicated.

Answer 3

 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 

Answer 4

We give 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' 

Answer 5

This is not exactly what you want, but you can use fixed ranges, for example:

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

Answer 6

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