I want query multi sub total in query sql. How query multi sub total. please help me.
SQL CODE:
SELECT MB.COUNTRY_CODE As C_CODE, MB.OPERATOR_NAME AS C_NAME, C.PMN_CODE AS OPER_NAME, (C.RANG_START / 1024) AS R_START, (C.RANG_END / 1024) AS R_END, COUNT(*) AS T_OPER, '0' AS TE_CHARGE, SUM(C.RATE) AS TR_CHARGE FROM IR_V_IMM_RATE_BY_CDR C LEFT JOIN IR_PMN_MASTER_BROKER MB ON C.COMPANY_CODE = MB.COMPANY_CODE AND C.PMN_DOMAIN = MB.PMN_CODE LEFT JOIN IR_PMN_INTER_SERVICE S ON C.PMN_DOMAIN = S.PMN_CODE AND C.COMPANY_CODE = S.COMPANY_CODE WHERE C.SUB_EVENT_TYPE_ID = '2' AND C.MODIFIER = 'P1P' AND S.INTER_SERVICE = 'MSS' AND C.COMPANY_CODE = 'ASS' AND C.EVENT_TYPE_ID = '622' AND C.PMN_DOMAIN = 'ACN' AND TO_CHAR(C.CUTOFF_DATE, 'MM/YYYY') = '04/2015' GROUP BY MB.COUNTRY_CODE, MB.OPERATOR_NAME, C.PMN_CODE, C.RANG_START, C.RANG_END, C.RATE
Result Query:
C_CODE | C_NAME | OPER_NAME | R_START | R_END | T_OPER | TE_CHARGE | TR_CHARGE 1 | Aic Ltd| AA | 1 | 10 | 1 | 0 | 0.8324 1 | Aic Ltd| AA | 3 | 22 | 5 | 0 | 2.312931 1 | Aic Ltd| BB | 4 | 20 | 2 | 0 | 10 1 | Aic Ltd| BB | 1 | 13 | 1 | 0 | 25
But i want add multi sub total in query.
I want Result Query:
C_CODE | C_NAME | OPER_NAME | R_START | R_END | T_OPER | TE_CHARGE | TR_CHARGE 1 | Aic Ltd| AA | 1 | 10 | 1 | 0 | 0.8324 1 | Aic Ltd| AA | 3 | 22 | 5 | 0 | 2.312931 | Total | 6 | 0 | 3.145331 ------------------------------------------------------------------------------ 1 | Aic Ltd| BB | 4 | 20 | 2 | 0 | 10 1 | Aic Ltd| BB | 1 | 13 | 1 | 0 | 25 | Total | 3 | 0 | 35 ------------------------------------------------------------------------------ Total | 9 | 0 | 38.145331 ------------------------------------------------------------------------------ 6 | SRI | CC | 2 | 11 | 2 | 0 | 1 6 | SRI | CC | 4 | 21 | 1 | 0 | 5 | Total | 3 | 0 | 6 -------------------------------------------------------------------------------- 6 | SRI | DD | 5 | 30 | 1 | 0 | 1.21 6 | SRI | DD | 2 | 40 | 1 | 0 | 2.35 | Total | 2 | 0 | 5.42 -------------------------------------------------------------------------------- Total | 5 | 0 | 11.42 --------------------------------------------------------------------------------
How add multi row sub total? Help me please.
Thanks in advance. 😉
Advertisement
Answer
I will post a solution with using the group by rollup extension. Please try this.
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, 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)
Though I think the answer provided Gordon Linoff pointed out is also very good answer and suggestion to this question.