June 3, 2016 at 3:35 am
Hi,
I want to group the following table but it must be grouped using a data range.
Difficult to explain, but the example makes is clear:
Table
Item Count
11 1
12 1
13 2
14 3
22 2
22 2
23 4
24 4
Result must be:
total of all items from 10 to 20
total of all items from 20 to 30
resulting table must be:
group Total
'10-20' 7
'20-30' 12
is this possible using a single SQL Statement?
June 3, 2016 at 3:53 am
WITH MyData AS (SELECT * FROM (VALUES (11,1),(12,1),(13,2),(14,3),(22,2),(22,2),(23,4),(24,4)) d (Item, [Count]))
SELECT
[10 to <20] = SUM(CASE WHEN Item >= 10 AND Item < 20 THEN [Count] ELSE 0 END),
[20 to <30] = SUM(CASE WHEN Item >= 20 AND Item < 30 THEN [Count] ELSE 0 END)
FROM MyData
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 3, 2016 at 7:29 am
Hi,
thanks for this interesting answer, however the result is one record with 2 fields,
I really need separate records per group because there are more fields involved in the real situation.
Is this possible?
this is your result:
10 to <20 20 to <30
7 12
Thanks
June 3, 2016 at 7:47 am
WITH MyData AS (SELECT * FROM (VALUES (11,1),(12,1),(13,2),(14,3),(22,2),(22,2),(23,4),(24,4)) d (Item, [Count]))
SELECT
[Range]='10 to <20', [Count]=SUM(CASE WHEN Item >= 10 AND Item < 20 THEN [Count] ELSE 0 END)
FROM MyData
UNION
SELECT
'20 to <30', SUM(CASE WHEN Item >= 20 AND Item < 30 THEN [Count] ELSE 0 END)
FROM MyData
June 3, 2016 at 8:15 am
There are actually several different approaches you can use. Here are two:
; -- Use a table value constructor with the ranges
WITH sample_data AS (
SELECT *
FROM (
VALUES
(11, 1)
,(12, 1)
,(13, 2)
,(14, 3)
,(22, 2)
,(22, 2)
,(23, 4)
,(24, 4)
) v(id, cnt)
)
SELECT range_desc, SUM(cnt)
FROM sample_data
INNER JOIN (
VALUES
(10, 20, '10-20')
,(20, 30, '20-30')
) ranges(lower_limit, upper_limit, range_desc)
ON id >= lower_limit
AND id < upper_limit
GROUP BY range_desc
; -- Use integer division
WITH sample_data AS (
SELECT *
FROM (
VALUES
(11, 1)
,(12, 1)
,(13, 2)
,(14, 3)
,(22, 2)
,(22, 2)
,(23, 4)
,(24, 4)
) v(id, cnt)
)
SELECT CAST(g.grp AS VARCHAR(5)) + '-' + CAST(g.grp + 10 AS VARCHAR(5)), SUM(cnt)
FROM sample_data
CROSS APPLY (VALUES(id/10*10)) g(grp)
GROUP BY g.grp
The first one is more flexible, but can be cumbersome if you have a lot of groups. The second is easier, but less flexible.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply