CODEWITHSUNDEEP
sqloracle-databasepivot
Imran Hemani
Imran Hemani

Reputation: 629

Add a total to SUM the pivoted columns in a cross-tab query

I have made a cross-tab query that lists the total stock on each of the location for a particular item.

 WITH PIVOT_DATA AS (
   SELECT A.ITEM, A.LOC, A.STOCK_ON_HAND, B.ITEM_DESC
   FROM   ITEM_LOC_SOH A, ITEM_MASTER B
   WHERE A.ITEM = B.ITEM
 )
 SELECT *
   FROM   pivot_data
   PIVOT (
     SUM( nvl(STOCK_ON_HAND,0) )        --<-- pivot_clause
     FOR LOC          --<-- pivot_for_clause
     IN  (
       15002 J12,
       16009 SPR,
       15008 J16,
       12002 JS1
     )   --<-- pivot_in_clause
 );

the result shows

ITEM  ITEM_DESC J12 SPR J16 JS1 
222   desc_xxx   2   4   5   6

I want a total to be added at the very end like:

ITEM  ITEM_DESC J12 SPR J16 JS1 Total 
222   desc_xxx   2   4   5   6   17

what do I add in the query?

NEW CODE:

WITH PIVOT_DATA AS 
(
SELECT A.ITEM, A.LOC, A.STOCK_ON_HAND, B.ITEM_DESC,IL.UNIT_RETAIL, B.BRAND_NAME, DECODE(B.STATUS, 'A', 'ACTIVE'), D.DEPT_NAME DEPARTEMENT, C.CLASS_NAME CLASS
FROM  ITEM_LOC_SOH A, ITEM_MASTER B, DEPS D, CLASS C, ITEM_LOC IL
WHERE A.ITEM = B.ITEM AND IL.ITEM = A.ITEM AND IL.ITEM = B.ITEM 
AND B.DEPT = D.DEPT AND B.DEPT = C.DEPT AND B.CLASS = C.CLASS AND D.DEPT = C.DEPT
AND A.ITEM = '00090909'

    UNION

    -- Insert "Totals" rows into the Cte
SELECT A.ITEM, 9999 AS Loc, SUM(A.STOCK_ON_HAND) AS STOCK_ON_HAND, B.ITEM_DESC,
il.unit_retail, B.BRAND_NAME, DECODE(B.STATUS, 'A', 'ACTIVE'), D.DEPT_NAME DEPARTEMENT
, C.CLASS_NAME CLASS
FROM   ITEM_LOC_SOH A, ITEM_MASTER B, DEPS D, CLASS C, ITEM_LOC IL
WHERE A.ITEM = B.ITEM AND IL.ITEM = A.ITEM AND IL.ITEM = B.ITEM AND A.ITEM = '00090909'
AND B.DEPT = D.DEPT AND B.DEPT = C.DEPT AND B.CLASS = C.CLASS AND D.DEPT = C.DEPT
GROUP BY A.ITEM, B.ITEM_DESC, IL.UNIT_RETAIL, B.BRAND_NAME, DECODE(B.STATUS, 'A', 'ACTIVE'), D.DEPT_NAME, C.CLASS_NAME
)
SELECT x.*
FROM   pivot_data
PIVOT 
(
    SUM(STOCK_ON_HAND)
    FOR LOC
    IN  
    (
       15002 J12,
       16009 SPR,
       15008 J16,
       12002 JS1,
       9999 Total
    )
 ) x;

Upvotes: 2

Views: 863

Answers (1)

StuartLC
StuartLC

Reputation: 107347

I can think of two ways to do this

  • Simply project the total as a new column by explicitly summing up the resulting PIVOT columns:


WITH PIVOT_DATA AS 
(
    SELECT A.ITEM, A.LOC, A.STOCK_ON_HAND, B.ITEM_DESC
    FROM   ITEM_LOC_SOH A INNER JOIN ITEM_MASTER B
    ON A.ITEM = B.ITEM
)
SELECT x.*, x.J12 + x.SPR + x.J16 + x.JS1 AS Totl
FROM   pivot_data
PIVOT 
(
    SUM(COALESCE(STOCK_ON_HAND, 0))
    FOR LOC
    IN  
    (
       15002 J12,
       16009 SPR,
       15008 J16,
       12002 JS1
    )
 ) x;

SqlFiddle

  • Or, alternatively, you can project the total into the data before it is pivoted, by adding it as a union to PIVOT_DATA cte:


WITH PIVOT_DATA AS 
(
    SELECT A.ITEM, A.LOC, A.STOCK_ON_HAND, B.ITEM_DESC
    FROM   ITEM_LOC_SOH A INNER JOIN ITEM_MASTER B
           ON A.ITEM = B.ITEM
)
,
TOTALS AS
(
      -- Add Insert "Totals" rows to the Cte
    SELECT ITEM, 9999 AS Loc, SUM(STOCK_ON_HAND) AS STOCK_ON_HAND, ITEM_DESC
    FROM PIVOT_DATA
    GROUP BY Item, ITEM_DESC
)
SELECT x.*
FROM
(SELECT * FROM pivot_data
 UNION 
 SELECT * FROM TOTALS
)
PIVOT 
(
    SUM(COALESCE(STOCK_ON_HAND, 0))
    FOR LOC
    IN  
    (
       15002 J12,
       16009 SPR,
       15008 J16,
       12002 JS1,
       9999 Total
    )
 ) x;

SqlFiddle here

Upvotes: 2

Related Questions