Help with Counting within a SQL Statement

I have a table that contains a field called RegionID. Values are stored in the field as comma-delimited string of numbers. It's an nvarchar.

A sample record might look like this:
1,2,5,6

Or simply this:
2

I'm running a count then grouping them by the RegionID but the records with more than one value are shewing the results.

I have a query like this:
SELECT RegionID, COUNT(RegionID) AS Total FROM Regions GROUP BY RegionID

My results are this:

RegionID    Total
1,2,6,5         1
2                  278
2,5,6,1         1
5                  36
5,2,6            1
6                  110

I preferrably want to have all the RegionID's counted individually.  I have 6 total RegionIDs so I really want the results above to be this:

RegionID    Total
1                  2
2                  281
5                  39
6                  113

Each comma-delimited value is counted individually.  

I hope I have somewhat explained what I need. Any help will be greatly appreciated!
greddinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tomasgruenerCommented:
The problem seems to be your DB Design... The result you got is correct, because you group over the full field and not over the single ID's...

I'm not sure, but I think there could be a way to do this grouping, but on cost of performance.

Why don't you use a relation table, where you fill in the connection between the two objects? It would be much easier to group those fields, and performance would be also better.
0
greddinAuthor Commented:
Yes, I agree. But I don't have any design control of the database. It's part of a much larger system. This is for an adhoc report so performance isn't going to be an issue.

If you have an ideas, please send them on.

Thanks.
0
ErnariashCommented:
I hope this will give you an idea

select c.RegionID, count(*)
from Regions as m
inner
join @RegionIDs as c
on ',' + m.RegionID + ','
like '%,' + c.RegionID + ',%'
Group by c.RegionID

DECLARE @RegionIDs    TABLE 
(  RegionID nvarchar(255))
insert into @RegionIDs
values ('1')
insert into @RegionIDs
values ('2')
insert into @RegionIDs
values ('3')
insert into @RegionIDs
values ('4')
insert into @RegionIDs
values ('5')
insert into @RegionIDs
values ('6')
select * from @RegionIDs
 
 
 
 
select c.RegionID, count(*)
  from Regions  as m
inner
  join @RegionIDs as c
    on  ',' + m.RegionID + ','
  like '%,' + c.RegionID + ',%'
 Group by c.RegionID
  

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

tomasgruenerCommented:
What db do you use? WIth MSSQL, if performance isn't an issue, I would create a temporary table, containing 2 columns. One for your tables id, the second for the related id.
Then programatically extract the date from them...

an example, how you could do that:
Create a function or procedure, that consumes the base table ID and the related ID field (the string with the delimited Ids).
in the function, you can use a split ( i think this one should work in TSQL: http://www.kodyaz.com/forums/thread/489.aspx ) to split the related ID's into record set...
After that, you can insert every record to the temp table (including the base table id)....
after that you can simply group the temp table by the related ID...

I hope you understand what I mean ;) I'm thinking of another solution, but right now I don't know any
0
ErnariashCommented:

If you have a table with unique or primary Key RegionIDs you dont need the temporary table just use your table instead of the @RegionIDs table&

select c.RegionID, count(*)
from Regions as m
inner join @RegionIDs as c
on ',' + m.RegionID + ','
like '%,' + c.RegionID + ',%'
Group by c.RegionID
Hope this help you...

select c.RegionID, count(*) 
from Regions as m 
inner join @RegionIDs as c 
on ',' + m.RegionID + ',' 
like '%,' + c.RegionID + ',%' 
Group by c.RegionID 

Open in new window

0
greddinAuthor Commented:
Guys thanks so much! You've given me some hope.  Ernariash, I tried your first sample above and looks like it's going to work, but I have a question. My "real" query is more complicated. I was trying to simplify for the post here but now I think I need to show you the query below.

The query below is what I have currently that displays the results as:

xRegion    Total
1,2,6,5       1
2                278
2,5,6,1       1
5                36
5,2,6          1
6                110

Can you help me merge your first example with the temp table in the query below so that it's using the xRegion field?  

SELECT ColMeta.xRegion, COUNT(ColMeta.xRegion) AS Total 
FROM Collections, ColMeta, ColExtra WHERE Collections.dCollectionID = ColExtra.dCollectionID AND 
Collections.dCollectionID = ColMeta.dCollectionID AND Collections.dParentCollectionId <> 1 AND dCollectionEnabled <> 0 AND 
ColExtra.dColIsProject = N'p' AND ((ColMeta.xDivision = N'2' OR ColMeta.xDivision LIKE(N'2,%') OR ColMeta.xDivision LIKE(N'2 %') OR 
ColMeta.xDivision LIKE(N'% 2') OR ColMeta.xDivision LIKE(N'%,2') OR ColMeta.xDivision LIKE(N'%, 2') OR ColMeta.xDivision LIKE(N'% 2,%') OR 
ColMeta.xDivision LIKE(N'% 2 %') OR ColMeta.xDivision LIKE(N'%,2,%')) AND (ColMeta.xIPFunctionalArea = N'1' OR ColMeta.xIPFunctionalArea 
LIKE(N'1,%') OR ColMeta.xIPFunctionalArea LIKE(N'1 %') OR ColMeta.xIPFunctionalArea LIKE(N'% 1') OR ColMeta.xIPFunctionalArea LIKE(N'%,1') OR 
ColMeta.xIPFunctionalArea LIKE(N'%, 1') OR ColMeta.xIPFunctionalArea LIKE(N'% 1,%') OR ColMeta.xIPFunctionalArea LIKE(N'% 1 %') OR 
ColMeta.xIPFunctionalArea LIKE(N'%,1,%'))) GROUP BY ColMeta.xRegion

Open in new window

0
ErnariashCommented:

Hello Greddin,
I will need to know your tables Collections, ColMeta and ColExtra to better to help you to optimize the querie.
Buy a quick fix could be the following.
@RegionIDs table could be any of your tables with  RegionID as unique or you could use somethig like this.
select c.RegionID, Sum(Total)
from #Regions as m
inner join (select Distict RegionIDs from YourIDTable) c
on ',' + m.RegionID + ','
like '%,' + c.RegionID + ',%'
Group by c.RegionID


SELECT ColMeta.xRegion, COUNT(ColMeta.xRegion) AS Total 
Into #Regions
FROM Collections, ColMeta, ColExtra 
WHERE Collections.dCollectionID = ColExtra.dCollectionID AND 
	Collections.dCollectionID = ColMeta.dCollectionID AND Collections.dParentCollectionId <> 1 AND dCollectionEnabled <> 0 AND 
	ColExtra.dColIsProject = N'p' AND ((ColMeta.xDivision = N'2' OR ColMeta.xDivision LIKE(N'2,%') OR ColMeta.xDivision LIKE(N'2 %') OR 
	ColMeta.xDivision LIKE(N'% 2') OR ColMeta.xDivision LIKE(N'%,2') OR ColMeta.xDivision LIKE(N'%, 2') OR ColMeta.xDivision LIKE(N'% 2,%') OR 
	ColMeta.xDivision LIKE(N'% 2 %') OR ColMeta.xDivision LIKE(N'%,2,%')) AND (ColMeta.xIPFunctionalArea = N'1' OR ColMeta.xIPFunctionalArea 
	LIKE(N'1,%') OR ColMeta.xIPFunctionalArea LIKE(N'1 %') OR ColMeta.xIPFunctionalArea LIKE(N'% 1') OR ColMeta.xIPFunctionalArea LIKE(N'%,1') OR 
	ColMeta.xIPFunctionalArea LIKE(N'%, 1') OR ColMeta.xIPFunctionalArea LIKE(N'% 1,%') OR ColMeta.xIPFunctionalArea LIKE(N'% 1 %') OR 
	ColMeta.xIPFunctionalArea LIKE(N'%,1,%')))
GROUP BY ColMeta.xRegion
 
select c.RegionID, Sum(Total) 
from #Regions as m 
inner join @RegionIDs as c 
on ',' + m.RegionID + ',' 
like '%,' + c.RegionID + ',%' 
Group by c.RegionID 

Open in new window

0
greddinAuthor Commented:
Ernariash:

Do I still need the syntax from the previous example to me this last one work?

When I run this last one (above) I get this error:

"Must declare the table variable "@RegionIDs".
0
ErnariashCommented:
You shoudl run table variable "@RegionIDs" at the same time as the Select...see query:

SELECT ColMeta.xRegion, COUNT(ColMeta.xRegion) AS Total 
Into #Regions 
FROM Collections, ColMeta, ColExtra 
WHERE Collections.dCollectionID = ColExtra.dCollectionID AND 
Collections.dCollectionID = ColMeta.dCollectionID AND Collections.dParentCollectionId <> 1 AND dCollectionEnabled <> 0 AND 
ColExtra.dColIsProject = N'p' AND ((ColMeta.xDivision = N'2' OR ColMeta.xDivision LIKE(N'2,%') OR ColMeta.xDivision LIKE(N'2 %') OR 
ColMeta.xDivision LIKE(N'% 2') OR ColMeta.xDivision LIKE(N'%,2') OR ColMeta.xDivision LIKE(N'%, 2') OR ColMeta.xDivision LIKE(N'% 2,%') OR 
ColMeta.xDivision LIKE(N'% 2 %') OR ColMeta.xDivision LIKE(N'%,2,%')) AND (ColMeta.xIPFunctionalArea = N'1' OR ColMeta.xIPFunctionalArea 
LIKE(N'1,%') OR ColMeta.xIPFunctionalArea LIKE(N'1 %') OR ColMeta.xIPFunctionalArea LIKE(N'% 1') OR ColMeta.xIPFunctionalArea LIKE(N'%,1') OR 
ColMeta.xIPFunctionalArea LIKE(N'%, 1') OR ColMeta.xIPFunctionalArea LIKE(N'% 1,%') OR ColMeta.xIPFunctionalArea LIKE(N'% 1 %') OR 
ColMeta.xIPFunctionalArea LIKE(N'%,1,%'))) 
GROUP BY ColMeta.xRegion 
 
DECLARE @RegionIDs TABLE 
( RegionID nvarchar(255)) 
insert into @RegionIDs 
values ('1') 
insert into @RegionIDs 
values ('2') 
insert into @RegionIDs 
values ('3') 
insert into @RegionIDs 
values ('4') 
insert into @RegionIDs 
values ('5') 
insert into @RegionIDs 
values ('6') 
 
select c.RegionID, Sum(Total) 
from #Regions as m 
inner join @RegionIDs as c 
on ',' + m.RegionID + ',' 
like '%,' + c.RegionID + ',%' 
Group by c.RegionID 

Open in new window

0
ErnariashCommented:
I have use an alias for the xRegion as RegionID

SELECT ColMeta.xRegion RegionID, COUNT(ColMeta.xRegion) AS Total 
Into #Regions 
FROM Collections, ColMeta, ColExtra 
WHERE Collections.dCollectionID = ColExtra.dCollectionID AND 
Collections.dCollectionID = ColMeta.dCollectionID AND Collections.dParentCollectionId <> 1 AND dCollectionEnabled <> 0 AND 
ColExtra.dColIsProject = N'p' AND ((ColMeta.xDivision = N'2' OR ColMeta.xDivision LIKE(N'2,%') OR ColMeta.xDivision LIKE(N'2 %') OR 
ColMeta.xDivision LIKE(N'% 2') OR ColMeta.xDivision LIKE(N'%,2') OR ColMeta.xDivision LIKE(N'%, 2') OR ColMeta.xDivision LIKE(N'% 2,%') OR 
ColMeta.xDivision LIKE(N'% 2 %') OR ColMeta.xDivision LIKE(N'%,2,%')) AND (ColMeta.xIPFunctionalArea = N'1' OR ColMeta.xIPFunctionalArea 
LIKE(N'1,%') OR ColMeta.xIPFunctionalArea LIKE(N'1 %') OR ColMeta.xIPFunctionalArea LIKE(N'% 1') OR ColMeta.xIPFunctionalArea LIKE(N'%,1') OR 
ColMeta.xIPFunctionalArea LIKE(N'%, 1') OR ColMeta.xIPFunctionalArea LIKE(N'% 1,%') OR ColMeta.xIPFunctionalArea LIKE(N'% 1 %') OR 
ColMeta.xIPFunctionalArea LIKE(N'%,1,%'))) 
GROUP BY ColMeta.xRegion 
 
DECLARE @RegionIDs TABLE 
( RegionID nvarchar(255)) 
insert into @RegionIDs 
values ('1') 
insert into @RegionIDs 
values ('2') 
insert into @RegionIDs 
values ('3') 
insert into @RegionIDs 
values ('4') 
insert into @RegionIDs 
values ('5') 
insert into @RegionIDs 
values ('6') 
 
select c.RegionID, Sum(Total) 
from #Regions as m 
inner join @RegionIDs as c 
on ',' + m.RegionID + ',' 
like '%,' + c.RegionID + ',%' 
Group by c.RegionID 

Open in new window

0
greddinAuthor Commented:
When I run the last one, I get this:

"Invalid column name 'RegionID'.

On Line 32 above.

Thanks for being so patient with me.
0
ErnariashCommented:
I have use an alias for the xRegion as RegionID, you can drop the temp table and recreate #Regions.

Drop table #Regions 
SELECT ColMeta.xRegion RegionID, COUNT(ColMeta.xRegion) AS Total 
Into #Regions 
FROM Collections, ColMeta, ColExtra 
WHERE Collections.dCollectionID = ColExtra.dCollectionID AND 
Collections.dCollectionID = ColMeta.dCollectionID AND Collections.dParentCollectionId <> 1 AND dCollectionEnabled <> 0 AND 
ColExtra.dColIsProject = N'p' AND ((ColMeta.xDivision = N'2' OR ColMeta.xDivision LIKE(N'2,%') OR ColMeta.xDivision LIKE(N'2 %') OR 
ColMeta.xDivision LIKE(N'% 2') OR ColMeta.xDivision LIKE(N'%,2') OR ColMeta.xDivision LIKE(N'%, 2') OR ColMeta.xDivision LIKE(N'% 2,%') OR 
ColMeta.xDivision LIKE(N'% 2 %') OR ColMeta.xDivision LIKE(N'%,2,%')) AND (ColMeta.xIPFunctionalArea = N'1' OR ColMeta.xIPFunctionalArea 
LIKE(N'1,%') OR ColMeta.xIPFunctionalArea LIKE(N'1 %') OR ColMeta.xIPFunctionalArea LIKE(N'% 1') OR ColMeta.xIPFunctionalArea LIKE(N'%,1') OR 
ColMeta.xIPFunctionalArea LIKE(N'%, 1') OR ColMeta.xIPFunctionalArea LIKE(N'% 1,%') OR ColMeta.xIPFunctionalArea LIKE(N'% 1 %') OR 
ColMeta.xIPFunctionalArea LIKE(N'%,1,%'))) 
GROUP BY ColMeta.xRegion 
 
DECLARE @RegionIDs TABLE 
( RegionID nvarchar(255)) 
insert into @RegionIDs 
values ('1') 
insert into @RegionIDs 
values ('2') 
insert into @RegionIDs 
values ('3') 
insert into @RegionIDs 
values ('4') 
insert into @RegionIDs 
values ('5') 
insert into @RegionIDs 
values ('6') 
 
select c.RegionID, Sum(Total) 
from #Regions as m 
inner join @RegionIDs as c 
on ',' + m.RegionID + ',' 
like '%,' + c.RegionID + ',%' 
Group by c.RegionID 

Open in new window

0
greddinAuthor Commented:
I'm sorry, maybe there's something I'm not understanding.

The code above still generates this error:

Invalid column name 'RegionID'.

Line 33.
0
ErnariashCommented:
Please do a select * from #Regions see the name of the columns.
If is shows "xRegion " Please use the query bellow. thanks.

DECLARE @RegionIDs TABLE 
( RegionID nvarchar(255)) 
insert into @RegionIDs 
values ('1') 
insert into @RegionIDs 
values ('2') 
insert into @RegionIDs 
values ('3') 
insert into @RegionIDs 
values ('4') 
insert into @RegionIDs 
values ('5') 
insert into @RegionIDs 
values ('6') 
 
select c.RegionID, Sum(Total) 
from #Regions as m 
inner join @RegionIDs as c 
on ',' + m.xRegion+ ',' 
like '%,' + c.RegionID + ',%' 
Group by c.RegionID 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
greddinAuthor Commented:
Are you available to help offline on this?
0
ErnariashCommented:
Are you using MS SQL? 2000,2005,2008?
0
greddinAuthor Commented:
This is MS SQL 2005.  I have a VNC client setup now on this computer.  If you don't mind, you could remote desktop into this computer where it is running.

0
ErnariashCommented:
ok, but I do not have VNC on my computer.
 
0
greddinAuthor Commented:
Do you have a chat program? I am greddin@yahoo.com using Yahoo Messenger.
0
greddinAuthor Commented:
You don't need a VNC client. You can use your browser. I can give you a url to hit.  
0
ErnariashCommented:
Ok
0
greddinAuthor Commented:
Do you have an email address or chat window I can send the vnc url to?  I would prefer not to post here.
0
ErnariashCommented:
I have sent you two emails at greddin@yahoo.com
0
greddinAuthor Commented:
Sorry for the late response. Thanks for you help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.