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

J_Carter

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

jclem1

ASKER

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?

jclem1

ASKER

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

jclem1

ASKER

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.

ASKER CERTIFIED SOLUTION

J_Carter

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jclem1

ASKER

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