asked on

SQL - How to get Count, Group by based on Like Message

Below query will execute a Table of records with Error, Error Types and its count.

I have to group by further based on Like messages in the ErrorType Column.

How should i do that.

create table #t 
(
    BillId int,
    StepName varchar(100),
    StepExec varchar(100),
    StepExecResult varchar(100),
    Created_date datetime
)

insert into #t 
values
    (1, 'Initiated', 'Taken Place','Pass', getdate()-10),
    (1, 'POS', 'Deadlock Error','Error', getdate()-9),
    (1, 'POS', 'Processed','Pass', getdate()-9),
    (1, 'Merchandise', 'Taken Place','Pass', getdate()-8),
    (1, 'verification', 'Webservice call error','Error', getdate()-7),
    (1, 'verification', 'Webservice call error','Error', getdate()-6),
    (1, 'verification', 'Webservice call','Pass', getdate()-5),
    (1, 'verification', 'Webservice Response','Error', getdate()-5),
    (1, 'verification', 'Webservice Response','Pass', getdate()-5),
    (1, 'verification', 'Timeout Error','Error', getdate()-5),
    (1, 'verification', 'Timeout Error','Error', getdate()-5),
    (1, 'verification', 'Timeout Error','Error', getdate()-5),
    (1, 'verification', '','Pass', getdate()-5),
    (1, 'Payment', 'calculationError','Error', getdate()-4),
    (1, 'Payment', 'calculationProcessed','Pass', getdate()-3),
    (1, 'Completed', 'Archived','Pass', getdate()-1),
    (2, 'Initiated', 'Taken Place','Pass', getdate()-10),
    (2, 'POS', 'Deadlock Error','Error', getdate()-9),
    (2, 'POS', 'Processed','Pass', getdate()-9),
    (2, 'Merchandise', 'Taken Place','Pass', getdate()-8),
    (2, 'verification', 'Webservice call error on 07/23/2016','Error', getdate()-7),
    (2, 'verification', 'Webservice call error on 07/24/2016','Error', getdate()-6),
    (2, 'verification', 'Webservice call','Pass', getdate()-5),
    (2, 'verification', 'Webservice Response','Error', getdate()-5),
    (2, 'verification', 'Webservice Response','Pass', getdate()-5),
    (2, 'verification', '','Pass', getdate()-5),
    (2, 'Payment', 'calculationProcessed','Pass', getdate()-3),
    (2, 'Completed', 'Archived','Pass', getdate()-1)

select 
    stepname, count(*) as ErrorCount 
from 
    #t
where 
    StepExecResult = 'Error'
group by 
    stepname


	SELECT  StepName ,
        SUM(COUNT(DISTINCT BillId)) OVER (PARTITION BY StepName) TotalStepError,
        StepExec ,
        COUNT(DISTINCT BillId) ErrorTypeCount
FROM    #t
WHERE   StepExecResult = 'Error'
GROUP BY StepName,
        StepExec


StepName	TotalStepError	StepExec			ErrorTypeCount
----------------------------------------------------------------
Payment			1			calculationError		1
POS				2			Deadlock Error			2
verification	6			Timeout Error			1
verification	6			Webservice call error	3
verification	6			Webservice Response		2

Open in new window

PortletPaul

"conditional aggregates" use a case expression INSIDE the aggregation function

COUNT( case when ErrorType like 'webservice%' then ErrorType end )

note: count only increments for a non-null value so don't count and provided a non-null else condition
e.g. alternatives are:

COUNT( case when ErrorType like 'webservice%' then 1 else NULL end )

SUM( case when ErrorType like 'webservice%' then 1 else 0 end )

Personally I prefer to use COUNT() instead of SUM() for this.

chokka

ASKER

Thanks Paul. But Error Type like .. will not be always Webservice. In my production data, I have around 100 different types of error. Almost 70% of errors have date time on it. So when i run the count, group by query .. it distinct the error based on date time also.

PortletPaul

I did not expect one example to cover all your needs.

What is "the expected result" from you sample data?

If you don't want rows by date/time use max(datetimecolumn) or min() depending on what you want.

Or perhaps
group by cast(datetimecol as date)
This would give rows by date only without time of day

chokka

ASKER

I think , It has to be some thing like this. I have created a Temp table with Like Type messages.

create table #t
(Stepname varchar(100),
TotalStepError int,
StepExec nvarchar(1000),
ErrorTypeCount int
)

insert into #t values
('Payment',1,'calculationError',1),
('POS',	2,	'Deadlock Error on 07/21/2016',	1),
('POS',	2,	'Deadlock Error on 07/22/2016',	1),
('verification',	6,	'Timeout Error',	1),
('verification',	6,	'Webservice call error',	1),
('verification',	6,	'Webservice call error on 07/23/2016',	1),
('verification',	6,	'Webservice call error on 07/24/2016',	1),
('verification',	6,	'Webservice Response',	2)


select * from #t



-- This is based on Like Type 

Create Table #ErrorLikeType
(
	TypeLike varchar(1000)
)

Insert into #ErrorLikeType values
('Webservice call error'),
('Timeout error')

select * from #ErrorLikeType

--Expected Result  - I have to write some kind of relation between my Temp Table and Like type table.

Stepname		TotalStepError	StepExec						ErrorTypeCount
---------------------------------------------------------------------------------
Payment				1			calculationError					1
POS					2			Deadlock Error						2
verification		6			Timeout Error						1
verification		6			Webservice call error				3
verification		6			Webservice Response					2

Open in new window

chaau

Why did you change the structure of your #t table?
I have originally made this query, but now it does not work with your new #t structure. Anyway, here is the query:

	SELECT  StepName ,
        SUM(COUNT(DISTINCT BillId)) OVER (PARTITION BY StepName) TotalStepError,
        e.TypeLike ,
        COUNT(DISTINCT BillId) ErrorTypeCount
FROM    #t t left join #ErrorLikeType e on t.StepExec like e.TypeLike + '%'
WHERE   StepExecResult = 'Error'
GROUP BY StepName,
        e.TypeLike

Open in new window

chokka

ASKER

Thank you Chaau. I haven't changed the original structure. I just added a new table as ErrorTypeLike.

I thought, that will simplify the issue.

ASKER CERTIFIED SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial