Need to group the result

There are 2 tables one contains present details and other once contains older details.I need to group into one common title.

Right Now i am getting result like

MSSQLSERVER.Application.pgTIPSR.17063General.Alert      2
MSSQLSERVER.Application.pgTIPSR.Error50000Severity16State1.Alert      2
MSSQLServer.Application.SQLServer.Themirroringconnectionhastimedout.Alert      3--
MSSQLServer.Application.SQLServer.Themirroringconnectionhastimedout.Alert      4-- need to be combined 3&4 to one as title are same
MSSQLSERVER.TMFSQL.MSSQLSERVER.Alert      15
SQLServerAgent.Application.SQLServer.SQLServerAgent208.Alert      5
StorageAgents.Hardware.StorageAgentsSYSTEM.Alert      1
StorageAgents.Hardware.StorageAgentsSYSTEMWARNING.Alert      1


My expected out is

MSSQLSERVER.Application.pgTIPSR.17063General.Alert      2
MSSQLSERVER.Application.pgTIPSR.Error50000Severity16State1.Alert      2
MSSQLServer.Application.SQLServer.Themirroringconnectionhastimedout.Alert      7 ------------------ It's should have 2 entries for same title.It's needs to grouped.
MSSQLSERVER.TMFSQL.MSSQLSERVER.Alert      15
SQLServerAgent.Application.SQLServer.SQLServerAgent208.Alert      5
StorageAgents.Hardware.StorageAgentsSYSTEM.Alert      1
StorageAgents.Hardware.StorageAgentsSYSTEMWARNING.Alert      1


Query:

SELECT SUBSTRING (SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc)),
      CHARINDEX('.', SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc))) + 1, 1000),
      COUNT (*)
FROM CT
WHERE pid =111 and  ticketdesc like '%sql%' and createddtim >='2012-03-11' and PATINDEX ('%Event[0-9]%', ticketdesc) > 0
And CHARINDEX (';  Desc:', ticketdesc) > 0
Group by SUBSTRING (SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc)),
      CHARINDEX('.', SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc))) + 1, 1000)
     
      union
      SELECT SUBSTRING (SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc)),
      CHARINDEX('.', SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc))) + 1, 1000),
      COUNT (*)
FROM T
WHERE pid =111 and  ticketdesc like '%sql%' and createddtim >='2012-03-11' and PATINDEX ('%Event[0-9]%', ticketdesc) > 0
And CHARINDEX (';  Desc:', ticketdesc) > 0
Group by SUBSTRING (SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc)),
      CHARINDEX('.', SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc))) + 1, 1000)
LVL 5
VIVEKANANDHAN_PERIASAMYAsked:
Who is Participating?
 
momi_sabagCommented:
try

select group_name, sum(cnt) from (
SELECT SUBSTRING (SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc)),
      CHARINDEX('.', SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc))) + 1, 1000) group_name,
      COUNT (*) cnt
FROM CT
WHERE pid =111 and  ticketdesc like '%sql%' and createddtim >='2012-03-11' and PATINDEX ('%Event[0-9]%', ticketdesc) > 0
And CHARINDEX (';  Desc:', ticketdesc) > 0
Group by SUBSTRING (SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc)),
      CHARINDEX('.', SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc))) + 1, 1000)
     
      union
      SELECT SUBSTRING (SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc)),
      CHARINDEX('.', SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc))) + 1, 1000),
      COUNT (*)
FROM T
WHERE pid =111 and  ticketdesc like '%sql%' and createddtim >='2012-03-11' and PATINDEX ('%Event[0-9]%', ticketdesc) > 0
And CHARINDEX (';  Desc:', ticketdesc) > 0
Group by SUBSTRING (SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc)),
      CHARINDEX('.', SUBSTRING (ticketdesc, PATINDEX ('%Event[0-9]%', ticketdesc), CHARINDEX (';  Desc:', ticketdesc) - PATINDEX ('%Event[0-9]%', ticketdesc))) + 1, 1000)
) t
group by group_name
0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
Awesome
0
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.

All Courses

From novice to tech pro — start learning today.