Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Group and Count from a table Expand / Collapse
Author
Message
Posted Thursday, March 14, 2013 10:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 8, 2013 6:15 AM
Points: 18, 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
Post #1431108
Posted Thursday, March 14, 2013 11:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:55 AM
Points: 2,873, Visits: 5,185
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1431157
Posted Thursday, March 14, 2013 11:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 8, 2013 6:15 AM
Points: 18, 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."
Post #1431165
Posted Thursday, March 14, 2013 11:52 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 9:51 PM
Points: 612, Visits: 2,858
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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"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

My blog
Post #1431168
Posted Thursday, March 14, 2013 11:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:55 AM
Points: 2,873, Visits: 5,185
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1431169
Posted Thursday, March 14, 2013 12:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 8, 2013 6:15 AM
Points: 18, 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


Post #1431184
Posted Thursday, March 14, 2013 12:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 8, 2013 6:15 AM
Points: 18, 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?
Post #1431186
Posted Thursday, March 14, 2013 1:21 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:55 AM
Points: 2,873, Visits: 5,185
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1431217
Posted Thursday, March 14, 2013 2:28 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 8, 2013 6:15 AM
Points: 18, 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
Post #1431266
Posted Thursday, March 14, 2013 3:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 8, 2013 6:15 AM
Points: 18, 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 !

Post #1431277
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse