Solved

how do i form the query to get different columns total count

Posted on 2016-11-16
13
68 Views
Last Modified: 2016-11-18
Hi Experts,

Table info:

tickets(ticket_id,type,created time,issuetype_id(p.k of issuetype),subcategory_id(p.k of categories)
issuetype(id(p.k),code)
issuetypereport(id,issue_name,code)
categories(catid,name)

total ticket_id count
total issue_name like threshold(count)
total issue_name like down(count)
group by cat id

Thanks,
0
Comment
Question by:srikotesh
[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
  • 6
  • 4
  • 3
13 Comments
 
LVL 2

Author Comment

by:srikotesh
ID: 41890857
one more count i have to get like

if
issue_name not like threshold
issue_name not like down

then it will be custom(count)
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41890879
Try..

SELECT COUNT(*) AS TicketIdCount
 , SUM( CASE WHEN itr.issue_name LIKE '%threshold%' THEN 1 ELSE 0 END ) AS ThresholdCount								
 , SUM( CASE WHEN itr.issue_name LIKE '%down%' THEN 1 ELSE 0 END ) AS DownCount								
 , SUM( CASE WHEN itr.issue_name NOT LIKE '%threshold%' THEN 1 ELSE 0 END ) AS NOTThresholdCount								 
, SUM( CASE WHEN itr.issue_name NOT LIKE '%down%' THEN 1 ELSE 0 END ) AS NOTDownCount
FROM 
tickets t 
	INNER JOIN issuetype it ON t.issuetype_id = it.id
	INNER JOIN issuetypereport itr ON itr.code = it.code
	INNER JOIN categories c ON c.catid = t.subcategory_id
GROUP BY c.catid

Open in new window


Hope it helps !
0
 
LVL 14

Assisted Solution

by:Pierre Cornelius
Pierre Cornelius earned 150 total points
ID: 41890925
@Pawan
Looks like he wants one count for custom so the last select in your sql would change to:
SUM( CASE WHEN (lower(itr.issue_name) NOT LIKE '%threshold%') and (lower(itr.issue_name) NOT LIKE '%down%')  THEN 1 ELSE 0 END ) AS CustomCount

Open in new window


Also note
  • I added lower function so case-sensitivy is not an issue
  • the table structure seems to be all in a 1-to-1 relationship, however, if that is not the case, this sql would not give proper results
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 29

Accepted Solution

by:
Pawan Kumar earned 350 total points
ID: 41890927
Great ! Thanks Pierre. As of now I have assumed 1:1, will see if we get any issue there.

Updated code.

SELECT COUNT(*) AS TicketIdCount
, SUM( CASE WHEN itr.issue_name LIKE '%threshold%' THEN 1 ELSE 0 END ) AS ThresholdCount								
, SUM( CASE WHEN itr.issue_name LIKE '%down%' THEN 1 ELSE 0 END ) AS DownCount								
,SUM( CASE WHEN (lower(itr.issue_name) NOT LIKE '%threshold%') and (lower(itr.issue_name) NOT LIKE '%down%')  THEN 1 ELSE 0 END ) AS CustomCount
FROM 
tickets t 
	INNER JOIN issuetype it ON t.issuetype_id = it.id
	INNER JOIN issuetypereport itr ON itr.code = it.code
	INNER JOIN categories c ON c.catid = t.subcategory_id
GROUP BY c.catid

Open in new window

0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 41891241
and the first 2 cases also need lower:
SELECT COUNT(*) AS TicketIdCount
, SUM( CASE WHEN lower(itr.issue_name) LIKE '%threshold%' THEN 1 ELSE 0 END ) AS ThresholdCount								
, SUM( CASE WHEN lower(itr.issue_name) LIKE '%down%' THEN 1 ELSE 0 END ) AS DownCount								
,SUM( CASE WHEN (lower(itr.issue_name) NOT LIKE '%threshold%') and (lower(itr.issue_name) NOT LIKE '%down%')  THEN 1 ELSE 0 END ) AS CustomCount
FROM 
tickets t 
	INNER JOIN issuetype it ON t.issuetype_id = it.id
	INNER JOIN issuetypereport itr ON itr.code = it.code
	INNER JOIN categories c ON c.catid = t.subcategory_id
GROUP BY c.catid

Open in new window

1
 
LVL 2

Author Comment

by:srikotesh
ID: 41892561
thanks
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41892571
Hi srikotesh,
Pierre just put lower to my solution and you selected his solution. Is this fair? Have you seen that i have solved the query for you?

Regards,
Pawan
0
 
LVL 2

Author Comment

by:srikotesh
ID: 41892574
yes,
custom count you have splitted as two different counts.
in middle he has suggested with his approach right.
0
 
LVL 2

Author Comment

by:srikotesh
ID: 41892575
sorry I wrongly added points
0
 
LVL 2

Author Comment

by:srikotesh
ID: 41892577
Hi admin,

could you please split the points to
pawan=350
peirr=150

I have added points wrongly.
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 41892580
I agree a split point would have been better. I have no objection if you wish to reopen and reallocate points.
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41892596
Thank you Srikotesh & Pierre !!

You can click on request attention button present after comment in the question section < ... > to do that.

Thanks !

Regards,
Pawan
0
 
LVL 2

Author Closing Comment

by:srikotesh
ID: 41893362
thanks
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

635 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