Group 123 and 123/1 in select query ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Group 123 and 123/1 in select query ?

Author

Topic

hztm2
Starting Member

16 Posts

Posted - 2010-05-20 : 08:17:00

Hi, I would be really grateful for any advice on how to achieve the following select query....

I have a table named Job with a list of Jobs as shown below. I want to get a count of the number of rows for each job - but I only want to look at the main job number before the slash - so I would get Job 123 = 3 and Job N123456=2.

JobNo
123
123/1
123/02
N123456
N123456/1

SELECT JobNo, COUNT(*) FROM tblJob
GROUP BY JobNo
ORDER BY JobNo

I do not know how I can handle the slashes in the group by.

Thank you very much

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-20 : 08:21:48

[code]
select left(JobNo, charindex('/', JobNo + '/') - 1), count(*)
from tblJob
group by left(JobNo, charindex('/', JobNo + '/') - 1)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-20 : 08:28:20

SELECT substring(JobNo,1,case when charindex('/',JobNo)>0 then charindex('/',JobNo)-1 else len(JobNo) end)
as JobNo,
COUNT(*) FROM tblJob
GROUP BY substring(JobNo,1,case when charindex('/',JobNo)>0 then charindex('/',JobNo)-1 else len(JobNo) end)
ORDER BY substring(JobNo,1,case when charindex('/',JobNo)>0 then charindex('/',JobNo)-1 else len(JobNo) end)

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-20 : 08:30:54

quote:
Originally posted by khtan

select left(JobNo, charindex('/', JobNo + '/') - 1), count(*)
from tblJob
group by left(JobNo, charindex('/', JobNo + '/') - 1)

KH
[spoiler]Time is always against us[/spoiler]

I take a bow!
That's better than mine

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-20 : 08:31:43

LOL

KH
[spoiler]Time is always against us[/spoiler]

hztm2
Starting Member

16 Posts

Posted - 2010-05-20 : 08:33:43

Hi - thank you very much for your response - this does not pick up the /02 row. Apart from that it is just what I am looking for. Is there anyway I can get it to pick up /1 /2 etc and also /01 /02 - I need to pick up anything after the slash.

Thanks again

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-20 : 08:35:26

quote:
Originally posted by hztm2

Hi - thank you very much for your response - this does not pick up the /02 row. Apart from that it is just what I am looking for. Is there anyway I can get it to pick up /1 /2 etc and also /01 /02 - I need to pick up anything after the slash.

Thanks again

what do you mean exactly by "pick up /1 /2 etc and also /01 /02" ?
Perhaps you can post the required result ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-20 : 08:37:09

or maybe this


select	JobNo, count(*) over (partition by left(JobNo, charindex('/', JobNo + '/') - 1))
from	tblJob

KH
[spoiler]Time is always against us[/spoiler]

hztm2
Starting Member

16 Posts

Posted - 2010-05-20 : 08:48:22

So sorry - I have been looking at this one for too long !!! Khtan your solution does exactly what I require - thank you and everyone very much indeed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources