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!
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!
ASKER
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.
If you have an ideas, please send them on.
Thanks.
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
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
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
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
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 ASKER
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?
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
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
ASKER
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".
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".
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
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
ASKER
When I run the last one, I get this:
"Invalid column name 'RegionID'.
On Line 32 above.
Thanks for being so patient with me.
"Invalid column name 'RegionID'.
On Line 32 above.
Thanks for being so patient with me.
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 ASKER
I'm sorry, maybe there's something I'm not understanding.
The code above still generates this error:
Invalid column name 'RegionID'.
Line 33.
The code above still generates this error:
Invalid column name 'RegionID'.
Line 33.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Are you available to help offline on this?
Are you using MS SQL? 2000,2005,2008?
ASKER
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.
ok, but I do not have VNC on my computer.
ASKER
Do you have a chat program? I am greddin@yahoo.com using Yahoo Messenger.
ASKER
You don't need a VNC client. You can use your browser. I can give you a url to hit.
Ok
ASKER
Do you have an email address or chat window I can send the vnc url to? I would prefer not to post here.
I have sent you two emails at greddin@yahoo.com
ASKER
Sorry for the late response. Thanks for you help.
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.