1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Pivot with grand total

Discussion in 'SQL PL/SQL' started by rosy, Oct 1, 2015.

  1. rosy

    rosy Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    vietnamese
    I have an SQL command as below
    Code (SQL):
    SELECT  *
    FROM
    (
    SELECT  NVL(Prime_Commodity,'total') AS CATEGORY,
    Location_Group WAREHOUSE,
    Stat_Year_No YEAR,
    /* Stat_Period_No PERIOD ,*/
    (Stat_Period_No+3) AS "MONTH",
    round(SUM(round(INVENTORY_PART_UNIT_COST_API.Get_Inventory_Value_By_Config(contract,Part_No,'*'),4)*Quantity ),4) AMOUNT
    FROM INVENT_VALUE_LOCGRP_SUM_EXT
    WHERE Contract = 'US'
    AND LENGTH(Part_No) = 11
    AND Quantity <> 0
    AND (Prime_Commodity = 'MM001'
    OR Prime_Commodity = 'MS001')
    AND Stat_Year_No BETWEEN nvl('&YEAR_F', 'yyyy' )  AND  nvl('&YEAR_T','yyyy')
    GROUP BY rollup(Prime_Commodity,Stat_Year_No,Stat_Period_No,Location_Group)
    /*order by Stat_Year_No,Stat_Period_No,Location_Group*/
    )
    PIVOT
    (
      SUM (AMOUNT)
      FOR   MONTH  IN (4,5,6,7,8,9,10,11,12)
    )

    ORDER BY CATEGORY
    Could every one help me add grand total to bottom of result. thank you
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Without create table statements and sample data it's impossible to even start on such a request. Using BREAK ON and COMPUTE won't work with PIVOT results; without a way to actually see the current results you have there's no way someone could even start deciphering what it is you're after.

    Define the problem in more detail and provide DDL and sample data.
     
    rosy likes this.
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    rosy likes this.