Count Number of items by groupings

Question

Post reply

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: 183686 More actions · Answer 1

Luis Cazares

SSC Guru

Points: 183686

March 13, 2014 at 11:06 am

#1696884

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

Nilssond

Hall of Fame

Points: 3285

March 13, 2014 at 11:31 am

#1696902

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: 183686 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: 183686 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

Nilssond

Hall of Fame

Points: 3285

March 13, 2014 at 3:13 pm

#1696965

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