Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query: Finding the Sum (oracle9i IBM AIX)
icon8.gif  SQL Query: Finding the Sum [message #314611] Thu, 17 April 2008 05:18 Go to next message
kekanap
Messages: 18
Registered: January 2008
Location: Centurion
Junior Member

Hallo Guys,

Need you help over here. Please have a look at my sql query and output below:

select d.login_id, A.action_code, trunc(b.date_time) as "DATE", c.case_reference, c.fraud_type_id, c.estimate_loss
from CM_ACTIONS A, CM_ACTIVITIES B, cm_users d, cm_tq_view_all c, cm_ticket e
where A.action_id = B.action_id
and d.USER_ID = b.user_ID
and c.CASE_HISTORY_ID = b.CASE_HISTORY_ID
and e.case_history_id = b.case_history_id
and e.case_history_id = c.case_history_id
and B.date_time > sysdate -31
and A.action_id in (1,3,12,15)
and d.user_id = 3
group by d.login_id, a.action_code, trunc(b.date_time), c.case_reference, c.fraud_type_id, c.ESTIMATE_LOSS
order by trunc(b.DATE_TIME), c.ESTIMATE_LOSS, d.login_id, a.action_code, trunc(b.date_time);


LOGIN_ID ACTION_CODE DATE CASE_REFERENCE FRAUD_TYPE_ID ESTIMATE_LOSS
hyman_c Assign 19/03/2008 0832090883 PD Summary alarm 29401.46
hyman_c Open 19/03/2008 0838521885 High Aggregate Cost of Calls 112457.79
hyman_c Open 01/04/2008 0838521885 High Aggregate Cost of Calls 112457.79
hyman_c Assign 07/04/2008 0782078038 Very Long Duration Call 0.19
hyman_c Assign 07/04/2008 0734646922 Very Long Duration Call 4
hyman_c Assign 07/04/2008 0730799413 Very Long Duration Call 50.13
hyman_c Assign 07/04/2008 0833063410 Very Long Duration Call 4261.97
hyman_c Open 07/04/2008 0832090883 PD Summary alarm 29401.46
hyman_c Open 07/04/2008 0838521885 High Aggregate Cost of Calls 112457.79
hyman_c Open 08/04/2008 0782078038 Very Long Duration Call 0.19
hyman_c Open 08/04/2008 0734646922 Very Long Duration Call 4
hyman_c Open 08/04/2008 0832090883 PD Summary alarm 29401.46
hyman_c Open 08/04/2008 0838521885 High Aggregate Cost of Calls 112457.79
hyman_c Open 09/04/2008 0734646922 Very Long Duration Call 4
hyman_c Open 09/04/2008 0730799413 Very Long Duration Call 50.13
hyman_c Open 09/04/2008 0833063410 Very Long Duration Call 4261.97
hyman_c Open 09/04/2008 0832090883 PD Summary alarm 29401.46
hyman_c Open 09/04/2008 0838521885 High Aggregate Cost of Calls 112457.79
hyman_c Open 10/04/2008 0782078038 Very Long Duration Call 0.19
hyman_c Open 10/04/2008 0734646922 Very Long Duration Call 4
hyman_c Open 10/04/2008 0730799413 Very Long Duration Call 50.13
hyman_c Open 10/04/2008 0833063410 Very Long Duration Call 4261.97
hyman_c Open 11/04/2008 0734646922 Very Long Duration Call 4
hyman_c Open 11/04/2008 0832090883 PD Summary alarm 29401.46
hyman_c Open 17/04/2008 0824115119 PD Summary alarm 118856.9


I would like to calculate the total sum(estimate loss) for all action codes. In short i would like to have another column
which will have something like below:

Total Loss for Open Tickets 807392.27
Total Loss for Closed Tickets 0
Total Loss for Assigned Tickets 33717.75

Can you please assist?

Regards,
Re: SQL Query: Finding the Sum [message #314612 is a reply to message #314611] Thu, 17 April 2008 05:22 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Spend some time in reading the forum guidelines before posting. For your requirement check the following link.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/aggreg.htm#i1007462

Regards

Raj
Previous Topic: Explain plan method
Next Topic: sql query
Goto Forum:
  


Current Time: Thu Dec 08 22:00:28 CST 2016

Total time taken to generate the page: 0.04970 seconds