Count Number of items by groupings

Question

Count Number of items by groupings

Nilssond

Hall of Fame

Points: 3285
More actions
March 13, 2014 at 10:54 am

#305283

Create table Clms(
ID varchar(11),
ServiceDate decimal(8,0))
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344455501', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344466601', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344466601', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344466601', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344466601', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344466601', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344466601', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344466601', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344466601', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344466601', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344466601', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344466601', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344466601', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344466601', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344466601', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344466601', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344466601', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344466601', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344466601', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344466601', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344466601', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344466601', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
INSERT INTO CLMS (ID, SERVICEDATE) VALUES('33344477701', 20140101)
Based on this data, I need to form results like this
Month Count Range
JAN 1 1-25
JAN 1 26-50
JAN 0 51-75
JAN 1 76-100
SQL Help needed

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Could you explain the logic to obtain the expected results?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Nilssond Hall of Fame Points: 3285 More actions · Answer 2

From the data in the table, I need to count the number of ID's that fall into a count range or bucket

which is incremented by 25, i.e. 1-25, 25-50, 51-75, 76-100......

by Month, which will be Jan and February 2014

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Do a favor to yourself and use date data types when needed. Using numeric values (or even string values) will only give you headaches.

Here's a possible solution for you (I'm assuming that you don't have a ranges table, but you really should have one):

WITH groupedCLMS AS(

SELECT ID, LEFT(ServiceDate, 6) ServiceMonth, COUNT(*) cnt, COUNT(*) / 25 grouper

FROM Clms

GROUP BY ID, LEFT(ServiceDate, 6)

)

SELECT STUFF( CONVERT(char(11), CONVERT( date, ServiceMonth + '01'), 100), 4, 3, '') ServiceMonth,

SUM(CASE WHEN grouper = N THEN 1 ELSE 0 END),

CAST( (N * 25) + 1 AS varchar(5)) + '-' + CAST( (N + 1) * 25 AS varchar(5))

FROM groupedCLMS g

CROSS

JOIN (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) Tally(N)

GROUP BY Tally.N, ServiceMonth

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Nilssond Hall of Fame Points: 3285 More actions · Answer 4

Nilssond

Hall of Fame

Points: 3285

March 13, 2014 at 2:41 pm

#1696960

Worked like magic... Thank you for your help!

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

It's great, but do you understand how does it work?

You'll be the one in charge of this for any modification, correction or improvement, so you better understand it. Feel free to ask any questions you have.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Nilssond Hall of Fame Points: 3285 More actions · Answer 6

Understood and thanks again. I had to expand the groupings to 30 to account for all the buckets we found.