Avatar of jclem1
jclem1Flag for United States of America asked on

SQL Sub Query Result Grouping

Experts

I have a simple select query that I use to produce reports on repeat trouble tickets.  This is the output of the query, also attached on the spreadsheet.  What I need in the reports column is the total of 1858+137 and then in the repeat_tickets column I want the 137 all on the same row.  The query I am using is also attached.

MONTH      SRC_SYS      VZ_SVC_TYPE_1      REPORTS      REPEAT_TICKETS
1/1/2009      WFA CP           DS0                        1858      
1/1/2009      WFA CP            DS0                        137             137

select trunc(CLOSED, 'MON')Month,SRC_SYS,vz_svc_type_1,count (1) Reports,
Case When (repeat)>=('1') then Count (1) end Repeat_tickets
from mtc.jc_rfr_mtd_2008
where trunc(closed,'MON')= '01-JAN-2009'
group by trunc(CLOSED, 'MON'),SRC_SYS,vz_svc_type_1,repeat

Open in new window

SQL-Example.xls
Enterprise SoftwareASPMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
jclem1

8/22/2022 - Mon
J_Carter

what is you uniuqe key for each of the tickets? i.e. how do you know they are the same?
ASKER
jclem1

I guess the unique key would be the ticket number but I wouldn't want to display it because I want the grouped by src_sys and vz_svc_type.
J_Carter

what I'm asking is how do you know it is a repeat ticket?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
jclem1

The repeat field = 1 if it is a repeat and 0 if it is not.
J_Carter

try this
select trunc(CLOSED, 'MON')Month,SRC_SYS,vz_svc_type_1,count (reports) as Reports, Count (repeat) as Repeat_tickets
from mtc.jc_rfr_mtd_2008
where trunc(closed,'MON')= '01-JAN-2009'
group by trunc(CLOSED, 'MON'),SRC_SYS,vz_svc_type_1

Open in new window

ASKER
jclem1

That gives me the exact same value in the reports and the repeat_tickets field, I only want to count the repeat field if the value =1, and there are only 2 options for it's value 0 or 1.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
J_Carter

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
jclem1

WOW that was simpler than I expected works perfect thank you.