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

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,
LVL 2
srikoteshAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
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
 
srikoteshAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Pierre CorneliusCommented:
@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
 
Pierre CorneliusCommented:
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
 
srikoteshAuthor Commented:
thanks
0
 
Pawan KumarDatabase ExpertCommented:
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
 
srikoteshAuthor Commented:
yes,
custom count you have splitted as two different counts.
in middle he has suggested with his approach right.
0
 
srikoteshAuthor Commented:
sorry I wrongly added points
0
 
srikoteshAuthor Commented:
Hi admin,

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

I have added points wrongly.
0
 
Pierre CorneliusCommented:
I agree a split point would have been better. I have no objection if you wish to reopen and reallocate points.
0
 
Pawan KumarDatabase ExpertCommented:
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
 
srikoteshAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.