CODEWITHSUNDEEP
sqloracle-databasedecode
Tinman
Tinman

Reputation: 63

ORACLE DECODE with multiple expressions or multiple decodes

I have the below data in an Oracle table which I'm trying to perform calculations based on TYPE & QT columns.

TYPE QT STATUS  CNT
---- -- ------  ---
E    A  YES     123
E    A  NO     2540
E    D  YES       2
E    D  NO      787
O    A  YES     884
O    A  NO     6957
O    D  YES      25
O    D  NO     1360

Looks like my expected output now is different.. like below :

TYPE    TOTAL   A   A&Y
----    -----   ----    -----
E+O     12678   10504   1007
E       3452     2663    123

My old oracle sql is below but the output is wrong when calculating the percentages:

SELECT * FROM (
    SELECT NVL(SUM(DECODE(A.QT,'A',A.CNT,'D',A.CNT)),0) AS "TOTAL",
    NVL(SUM(DECODE(A.QT,'A',A.CNT)),0) AS "A ALL",
    NVL(SUM(DECODE(A.QT,'A',ROUND(100*(NVL(A.CNT,0)/ A.CNT),2))),0) "A ALL %",
    NVL(SUM(DECODE(A.STATUS,'E',A.CNT)),0) AS "E TOTAL",
    NVL(SUM(DECODE(A.STATUS,'E',DECODE(A.QT,'A',A.CNT))),0) AS "E & A TOTAL"",
    NVL(SUM(DECODE(A.STATUS,'E',ROUND(100 * SUM((DECODE(A.QT,'A',A.CNT)) / SUM(A.CNT)),3))),0) "E & A %"
    FROM ALLIN A)

Upvotes: 0

Views: 16976

Answers (2)

d r
d r

Reputation: 7846

Maybe you could consider using Case expressions instead of Decode() function. The Decode() function could be a bit faster but the readability is always in favour of Case expressions:

Select  total, a_all, Round( a_all * 100 / total, 2) a_all_pct,
        e_total, ea_total, Round( ea_total * 100 / e_total, 2) ea_pct
From ( SELECT   SUM(cnt) as total,
                SUM(Case When qt = 'A'          Then cnt   Else  0 End) as a_all,
                SUM(Case When type = 'E'        Then cnt   Else  0 End) as t_total, 
                SUM(Case When type || qt = 'EA' Then cnt   Else  0 End) as ea_total
       FROM allin )

/*      R e s u l t :
TOTAL   A_ALL   A_ALL_PCT   E_TOTAL   EA_TOTAL    EA_PCT
-----  ------  ----------  --------  ---------  --------
12678   10504       82.85      3452       2663     77.14      */

... the same result using just one query (no subquery - no cte)

Select   SUM(cnt) as total,
         SUM(Case When qt = 'A'                 Then cnt   Else  0 End) as a_all,
         Round( SUM(Case When qt = 'A'          Then cnt   Else  0 End) * 100 / SUM(cnt), 2 ) as a_all_pct,
         --
         SUM(Case When type = 'E'               Then cnt   Else  0 End) as e_total, 
         SUM(Case When type || qt = 'EA'        Then cnt   Else  0 End) as ea_total, 
         Round( SUM(Case When type || qt = 'EA' Then cnt   Else  0 End) * 100 / SUM(Case When type = 'E' Then cnt   Else  0 End), 2 ) as ea_pct
From allin

See the fiddle here.

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 181047

You can rewrite the query in a little more readable way using a common table expression; 

WITH cte AS (
  SELECT 
    SUM(cnt) total,
    SUM(DECODE(qt,   'A', cnt, 0))      a_all,
    SUM(DECODE(type, 'E', cnt, 0))      e_total,
    SUM(DECODE(type||qt, 'EA', cnt, 0)) ea_total
  FROM allin
)
SELECT 
  total, a_all, ROUND(a_all/total*100, 2) a_percent,
  e_total, ea_total, ROUND(ea_total/e_total*100, 2) ea_percent
FROM cte;

This query generates the expected result and is a bit easier to read and debug. The EA part is assuming that qt and type are each one letter, so a concatenation of them matches EA. If that is not true, I recommend using a CASE instead, a'la;

SUM(CASE WHEN type = 'E' AND qt = 'A' THEN cnt ELSE 0 END) ea_total

An SQLfiddle to test with.

Upvotes: 0

Related Questions