Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2016-11-16
13
Medium Priority
?
74 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 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 35

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 600 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 35

Accepted Solution

by:
Pawan Kumar earned 1400 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 35

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 35

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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

885 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