CODEWITHSUNDEEP
sqlsql-servert-sql
David_S53
David_S53

Reputation: 83

Grouping fields to provide a count of values in a range

Looking to group values held in a table into differing bands based on the Type of Value.

So an example source table would look like this:

+------+-------+
| Type | Value |
+------+-------+
|   A  |  123  |
+------+-------+
|   A  |  992  |
+------+-------+
|   B  |  1432 |
+------+-------+
|   C  |  2314 |
+------+-------+
|   C  |  2982 |
+------+-------+
|   C  |  1879 |
+------+-------+
|   C  |  3473 |
+------+-------+
|   D  |  3190 |
+------+-------+
|   D  |  1920 |
+------+-------+

Output I'd like from a query would then group Values by 1000's and count the number of Types:

+------+-----------+-------+
| Type |   Values  | Count |
+------+-----------+-------+
|   A  |   0-999   |   2   |
+------+-----------+-------+
|   B  | 1000-1999 |   1   |
+------+-----------+-------+
|   C  | 1000-1999 |   1   |
+------+-----------+-------+
|   C  | 2000-2999 |   2   |
+------+-----------+-------+
|   C  | 3000-3999 |   1   |
+------+-----------+-------+
|   D  | 1000-1999 |   1   |
+------+-----------+-------+
|   D  | 3000-3999 |   1   |
+------+-----------+-------+

Have previously used a CASE to band the Values and then group and count the types like this:

select tblB.[Type], tblB.[Values], count(tblB.[Values]) [Count] 
from (
    select [Type], 
        case 
            when [Value] between 0 and 999 then '0-999'
            when [Value] between 1000 and 1999 then '1000-1999'
            when [Value] between 2000 and 2999 then '2000-2999'
            when [Value] between 3000 and 3999 then '3000-3999'
        end [Values]
    from tblA
) tblB 
group by tblB.[Type], tblB.[Values]

But am now looking at a very large number of values into the 100,000's so structuring a CASE to match doesn't appear as efficient.

Keen to know if there is a more dynamic way of doing this?

Upvotes: 2

Views: 129

Answers (1)

Squirrel
Squirrel

Reputation: 24783

as what TT commented. GROUP BY Value / 1000. Assuming that Value is integer.

select t.[Type], v.[Values], [Count] = COUNT(*)
from   tbl t
       cross apply
       (
            select [Values] = convert(varchar(10), [Value] / 1000 * 1000)
                            + ' - '
                            + convert(varchar(10), (([Value] / 1000) + 1) * 1000)
       ) v
group by t.[Type], v.[Values]
order by t.[Type], v.[Values]

Upvotes: 2

Related Questions