Solved

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

Posted on 2016-11-16
13
42 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
  • 6
  • 4
  • 3
13 Comments
 
LVL 1

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 24

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
 
LVL 24

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 1

Author Comment

by:srikotesh
ID: 41892561
thanks
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 24

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 1

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 1

Author Comment

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

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 24

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 1

Author Closing Comment

by:srikotesh
ID: 41893362
thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

912 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

17 Experts available now in Live!

Get 1:1 Help Now