Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
Last Comment
chaau
8/22/2022 - Mon
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
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 1POS 2 Deadlock Error 2verification 6 Timeout Error 1verification 6 Webservice call error 3verification 6 Webservice Response 2
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) ErrorTypeCountFROM #t t left join #ErrorLikeType e on t.StepExec like e.TypeLike + '%'WHERE StepExecResult = 'Error'GROUP BY StepName, e.TypeLike
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.