Solved

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

Posted on 2016-07-31
7
29 Views
Last Modified: 2016-08-28
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

0
Comment
Question by:chokka
  • 3
  • 2
  • 2
7 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41736788
"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.
0
 

Author Comment

by:chokka
ID: 41736797
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41736803
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
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:chokka
ID: 41736887
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

0
 
LVL 24

Expert Comment

by:chaau
ID: 41736910
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

0
 

Author Comment

by:chokka
ID: 41736912
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.
0
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 41736915
No, you have:
Original table:
create table #t 
(
    BillId int,
    StepName varchar(100),
    StepExec varchar(100),
    StepExecResult varchar(100),
    Created_date datetime
)

Open in new window


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

Open in new window

But anyway, you'll figure out how to adjust the query
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now