SQLTeam.com | Weblogs | Forums

Oracle Sql: How to add string 'Sub Total' in sql?


#1

I want Show string 'Sub total' in query but show 'Total' Only.
Please help me.

Sql Code:

with Src as (
select  1 C_CODE, 'Aic Ltd' C_NAME, 'AA' OPER_NAME, 1 R_START, 10 R_END, 1 T_OPER, 0 TE_CHARGE, 0.8324 TR_CHARGE from dual union all
select  1 C_CODE, 'Aic Ltd' C_NAME, 'AA' OPER_NAME, 3 R_START, 22 R_END, 5 T_OPER, 0 TE_CHARGE, 2.312931 TR_CHARGE from dual union all

select  1 C_CODE, 'Aic Ltd' C_NAME, 'BB' OPER_NAME, 4 R_START, 20 R_END, 2 T_OPER, 0 TE_CHARGE, 10 TR_CHARGE from dual union all
select  1 C_CODE, 'Aic Ltd' C_NAME, 'BB' OPER_NAME, 1 R_START, 13 R_END, 1 T_OPER, 0 TE_CHARGE, 25 TR_CHARGE from dual union all

select  6 C_CODE, 'SRI' C_NAME, 'CC' OPER_NAME, 2 R_START, 11 R_END, 2 T_OPER, 0 TE_CHARGE, 1 TR_CHARGE from dual union all
select  6 C_CODE, 'SRI' C_NAME, 'CC' OPER_NAME, 4 R_START, 21 R_END, 1 T_OPER, 0 TE_CHARGE, 5 TR_CHARGE from dual union all

select  6 C_CODE, 'SRI' C_NAME, 'DD' OPER_NAME, 5 R_START, 30 R_END, 1 T_OPER, 0 TE_CHARGE, 1.21 TR_CHARGE from dual union all
select  6 C_CODE, 'SRI' C_NAME, 'DD' OPER_NAME, 2 R_START, 40 R_END, 1 T_OPER, 0 TE_CHARGE, 2.35 TR_CHARGE from dual
)

select c_code, 
       c_name, 
       (CASE WHEN GROUPING(oper_name) = 1 THEN 'Grand Total' ELSE oper_name END) AS oper_name,
       r_start, 
       R_End, 
       sum(T_OPER), 
       sum(TE_CHARGE),
       sum(tr_charge)
  from src
 group by rollup(c_code, c_name, oper_name, r_start, R_End)
having grouping(c_name) = 0 and (grouping(r_Start) = 1 or grouping(R_End) = 1)

#2

This forum is specific to MS SQL Server. Post Oracle related questions at www.orafaq.com


#3

Removing all occurrences of "From Dual" allows this to work in SQL Server 2008 with no problem so thank you very much for posting nearly readily consumable date.

The thing that I'm confused about is that you say you want it to show "Sub Total" but I only see "Grand Total" in the code.

If you want help on this, I'll need a bit more information. Thanks.