Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Help with Counting within a SQL Statement

Posted on 2008-11-07
24
Medium Priority
?
237 Views
Last Modified: 2012-05-05
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!
0
Comment
Question by:greddin
  • 11
  • 11
  • 2
24 Comments
 
LVL 2

Expert Comment

by:tomasgruener
ID: 22905037
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
 

Author Comment

by:greddin
ID: 22905190
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
 
LVL 9

Expert Comment

by:Ernariash
ID: 22905377
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:tomasgruener
ID: 22905411
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
 
LVL 9

Expert Comment

by:Ernariash
ID: 22905768

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
 

Author Comment

by:greddin
ID: 22905853
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
 
LVL 9

Expert Comment

by:Ernariash
ID: 22906292

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
 

Author Comment

by:greddin
ID: 22908718
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
 
LVL 9

Expert Comment

by:Ernariash
ID: 22908761
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
 
LVL 9

Expert Comment

by:Ernariash
ID: 22908774
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
 

Author Comment

by:greddin
ID: 22908840
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
 
LVL 9

Expert Comment

by:Ernariash
ID: 22909112
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
 

Author Comment

by:greddin
ID: 22909278
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
 
LVL 9

Accepted Solution

by:
Ernariash earned 2000 total points
ID: 22909294
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
 

Author Comment

by:greddin
ID: 22909296
Are you available to help offline on this?
0
 
LVL 9

Expert Comment

by:Ernariash
ID: 22909320
Are you using MS SQL? 2000,2005,2008?
0
 

Author Comment

by:greddin
ID: 22909326
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
 
LVL 9

Expert Comment

by:Ernariash
ID: 22909365
ok, but I do not have VNC on my computer.
 
0
 

Author Comment

by:greddin
ID: 22909378
Do you have a chat program? I am greddin@yahoo.com using Yahoo Messenger.
0
 

Author Comment

by:greddin
ID: 22909463
You don't need a VNC client. You can use your browser. I can give you a url to hit.  
0
 
LVL 9

Expert Comment

by:Ernariash
ID: 22909475
Ok
0
 

Author Comment

by:greddin
ID: 22909482
Do you have an email address or chat window I can send the vnc url to?  I would prefer not to post here.
0
 
LVL 9

Expert Comment

by:Ernariash
ID: 22909498
I have sent you two emails at greddin@yahoo.com
0
 

Author Closing Comment

by:greddin
ID: 31514376
Sorry for the late response. Thanks for you help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Integration Management Part 2
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question