At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.
Course Of The Month
9 days, 5 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL - How to get Count, Group by based on Like Message
Posted on 2016-07-31
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.
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
2
7 Comments
Active today
"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.
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.
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.
Active today
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
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 1
POS 2 Deadlock Error 2
verification 6 Timeout Error 1
verification 6 Webservice call error 3
verification 6 Webservice Response 2
Active 7 days ago
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:
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
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.
I thought, that will simplify the issue.
Active 7 days ago
No, you have:
Original table:
New table:
Original table:
create table #t
(
BillId int,
StepName varchar(100),
StepExec varchar(100),
StepExecResult varchar(100),
Created_date datetime
)
New table:
create table #t
(Stepname varchar(100),
TotalStepError int,
StepExec nvarchar(1000),
ErrorTypeCount int
)
Featured Post
Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article explains how to reset the password of the sa account on a Microsoft SQL Server. The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month9 days, 5 hours left to enroll
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.