SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Group and Count from a table


Group and Count from a table

Author
Message
romah
romah
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 67
I have a following table and I need to group by each topic and count them.

SNo Category
1 ------- XML/HTML
2 ------- ASP.NET
3 ------- C#
4 ------- ASP.NET/C#
5 ------- C#/XML
6 ------- HTML/ASP.NET
7 ------- SQL
8 ------- SQL/HTML
9 ------- SQL/XML
10 ------- XML
11 ------- C#
12 ------- ASP.NET
13 ------- SQL
14 ------- XML
15 ------- SQL

Expected Result:

Category Count
C# -------- 4
XML -------- 5
HTML ------- 3
ASP.NET --- 4
SQL ------- 5
Eugene Elutin
Eugene Elutin
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4968 Visits: 5478
With Jeff Moden help ( dbo.DelimitedSplit8K string split function - http://www.sqlservercentral.com/articles/Tally+Table/72993/ )

you can do just this:


declare @mytable table (SNo int, Category varchar(100))
insert @mytable
select 1, 'XML/HTML'
union select 2, 'ASP.NET'
union select 3, 'C#'
union select 4, 'ASP.NET/C#'
union select 5, 'C#/XML'
union select 6, 'HTML/ASP.NET'
union select 7, 'SQL'
union select 8, 'SQL/HTML'
union select 9, 'SQL/XML'
union select 10, 'XML'
union select 11, 'C#'
union select 12, 'ASP.NET'
union select 13, 'SQL'
union select 14, 'XML'
union select 15, 'SQL'

select d.Item as Category, COUNT(*) AS Count
from @mytable t
cross apply dbo.DelimitedSplit8K (Category, '/') d
group by d.Item



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
romah
romah
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 67
Thank you SSCrazy for your quick reply.

Now, I got the following error:

"Category" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90."
Alan.B
Alan.B
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5119 Visits: 7703
Well, Eugene beat me to it and with something better than what I put together.


--Next time provide some DDL like so....

--1) Lets setup that test data
IF OBJECT_ID('tempdb..#classes') IS NOT NULL
DROP TABLE #classes;

CREATE TABLE #classes (SNo int primary key, category varchar(20) NOT NULL);

INSERT INTO #classes
SELECT 1,'XML/HTML' UNION ALL
SELECT 2,'ASP.NET' UNION ALL
SELECT 3,'C#' UNION ALL
SELECT 4,'ASP.NET/C#' UNION ALL
SELECT 5,'C#/XML' UNION ALL
SELECT 6,'HTML/ASP.NET' UNION ALL
SELECT 7,'SQL' UNION ALL
SELECT 8,'SQL/HTML' UNION ALL
SELECT 9,'SQL/XML' UNION ALL
SELECT 10,'XML' UNION ALL
SELECT 11,'C#' UNION ALL
SELECT 12,'ASP.NET' UNION ALL
SELECT 13,'SQL' UNION ALL
SELECT 14,'XML' UNION ALL
SELECT 15,'SQL'

-- The query:
;WITH cat1 AS
( SELECT category FROM #classes WHERE CHARINDEX('/',category)=0),
cat2 AS
( SELECT category FROM #classes WHERE CHARINDEX('/',category)<>0),
catall AS
( SELECT category FROM cat1
UNION ALL
SELECT SUBSTRING(Category,1,CHARINDEX('/',Category)-1) FROM cat2
UNION ALL
SELECT SUBSTRING(Category,CHARINDEX('/',Category)+1,LEN(category)) FROM cat2)
SELECT category, count(category) AS [count]
FROM catall
GROUP BY category

-- be nice to your dba
DROP TABLE #classes
GO



-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Eugene Elutin
Eugene Elutin
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4968 Visits: 5478
romah (3/14/2013)
Thank you SSCrazy for your quick reply.

Now, I got the following error:

"Category" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90."


Can you post what you're running to get this error?
Which Sql Server version are you using?
What is Category in your case?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
romah
romah
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 67
Actually I copied your code and also a function

declare @mytable table (SNo int, Category varchar(100))
insert @mytable
select 1, 'XML/HTML'
union select 2, 'ASP.NET'
union select 3, 'C#'
union select 4, 'ASP.NET/C#'
union select 5, 'C#/XML'
union select 6, 'HTML/ASP.NET'
union select 7, 'SQL'
union select 8, 'SQL/HTML'
union select 9, 'SQL/XML'
union select 10, 'XML'
union select 11, 'C#'
union select 12, 'ASP.NET'
union select 13, 'SQL'
union select 14, 'XML'
union select 15, 'SQL'

select d.Item as Category, COUNT(*) AS Count
from @mytable t
cross apply dbo.fnSplit(Category, '/') d
group by d.Item

When I tried with your code, I got following error:
"Category" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.


Also tried to use my table TestCategory

select d.Item as Category, COUNT(*) AS Count
from TestCategory t
cross apply dbo.fnSplit(Category, '/') d
group by d.Item

When I did like this, I got syntax error 'Category'


My table is very simple as I already provided.

SNo---Category
1 ------- XML/HTML
2 ------- ASP.NET
3 ------- C#
4 ------- ASP.NET/C#
5 ------- C#/XML
6 ------- HTML/ASP.NET
7 ------- SQL
8 ------- SQL/HTML
9 ------- SQL/XML
10 ------- XML
11 ------- C#
12 ------- ASP.NET
13 ------- SQL
14 ------- XML
15 ------- SQL
romah
romah
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 67
Awesome Alan's code works perfect !!!

I changed "#classes" to my table name "TestCategory" and it gave the result that I expected.

Thank you Alan. and also thank you Eugene Elutin.

How to accept that post as answer?
Eugene Elutin
Eugene Elutin
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4968 Visits: 5478
Note, dbo.DelimitedSplit8K is the fastest way to split sting in T-SQL (it only will marginally loose to CLR implementation). Also, what will happen if your Category will include 3 or more skills: HTML/ASP.NET/C#?
You will need to amend Alan's solution in order to deal with this (making it slower). Using DelimitedSplit8K splitter, you don't need to worry about this and scalability.
Try to make it working. I guess you have some other object in your database called Category. Try to put table alias at front of Category inside of function:


select d.Item as [Category], COUNT(*) AS Count
from @mytable t
cross apply dbo.fnSplit(t.[Category], '/') d
group by d.Item



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
romah
romah
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 67
I tried and I got another error for both @mytable and TestCategory

Error:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.

And I tried with select after cross apply. Again got the same error.


select D.Item as [Category], COUNT(*) AS Count
from dbo.TestCategory as T
cross apply
(
select * from dbo.fnSplit(T.[Category],'/')
) as D
group by D.Item


I test the fnSplit() function and it's returning correct values

select * from dbo.fnSplit('asp.net/c#/xml/html','/')

Returns
Item Number---Item
1----------------asp.net
2----------------c#
3----------------xml
4----------------html
romah
romah
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 67
I found in other forums that "cross apply" only works if the compatibility_level is greater than 80.

When I checked the compatibility_level of our database, it's 80.


SELECT compatibility_level
FROM sys.databases WHERE name = 'database_name'



I think Eugene's solution works in greater compatibility-level but I don't have permission to change it.

Thanks once again Eugene !:-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search