Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 78
  • Last Modified:

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,
0
srikotesh
Asked:
srikotesh
  • 6
  • 4
  • 3
2 Solutions
 
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
 
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now