T-SQL for counting rows

Question

T-SQL for counting rows

mike.kamish

SSC Rookie

Points: 38
More actions
February 15, 2018 at 10:00 am

#344050

I have a data table "T" with data that looks like this:
Class ScheduledDate
A 6/1/2018
B 6/5/2018
A 7/1/2018
A 8/1/2018
I know I can usesoemthing "Select Class, Schedule from T order by Class desc, ScheduledDate" to get an ordered list. What I'd really like to do, however is also show a session counter, something like this:
B (session 1) 6/5/2018
A (session 1) 6/1/2018
A (session 2) 7/1/2018
A (session 3) 8/2/2015
I tried using "select (class + ' (Session ' + cast((Row_Number() Over ( Order By ScheduledDate )) as varchar)+')') as Title but that gives me the wrong session number. Somehow I need to use a "group by" the Class name but I can't seem to figure it out.
Any ideas? It would be appreciated. Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

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

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

mike.kamish - Thursday, February 15, 2018 10:00 AM
I have a data table "T" with data that looks like this:
Class ScheduledDate
A 6/1/2018
B 6/5/2018
A 7/1/2018
A 8/1/2018
I know I can usesoemthing "Select Class, Schedule from T order by Class desc, ScheduledDate" to get an ordered list. What I'd really like to do, however is also show a session counter, something like this:
B (session 1) 6/5/2018
A (session 1) 6/1/2018
A (session 2) 7/1/2018
A (session 3) 8/2/2015
I tried using "select (class + ' (Session ' + cast((Row_Number() Over ( Order By ScheduledDate )) as varchar)+')') as Title but that gives me the wrong session number. Somehow I need to use a "group by" the Class name but I can't seem to figure it out.
Any ideas? It would be appreciated. Thanks!

OVER clause uses PARTITION BY instead of GROUP BY.
select (class + ' (Session ' + cast((Row_Number() Over ( PARTITION BY class ORDER By ScheduledDate )) as varchar)+')') as Title

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

mike.kamish SSC Rookie Points: 38 More actions · Answer 2

Fantastic! Just tried it and it worked perfectly ... and I learned something new: Partitioning.
Thank you so much.

subramaniam.chandrasekar SSCarpal Tunnel Points: 4223 More actions · Answer 3

mike.kamish - Thursday, February 15, 2018 1:02 PM
Fantastic! Just tried it and it worked perfectly ... and I learned something new: Partitioning.
Thank you so much.

that's really nice