CODEWITHSUNDEEP
sqloracle-databaseaggregate-functionspercentage
carlos
carlos

Reputation: 63

How can I display the percentage of my columns

I want to calculate the percentage in each month for each typeItem in relation with all typeItem, how can I achieve this?

I have a table with typeItem, AMOUNT, month in the folowwing way

SELECT
    SUM(CASE WHEN MONTH = 1 THEN AMOUNT END) "JAN"
    SUM(CASE WHEN MONTH = 2 THEN AMOUNT END) "FEB"
...
FROM (SELECT AMOUNT, MONTH, ItemTYPE FROM MY TABLE)
GROUP BY typeItem

WHICH GIVES ME 

OUTPUT:
ItemType  JAN   FEB  ...

A         123   456
B         234   567
...

--BUT I WANT:

SELECT
    SUM(CASE WHEN MONTH = 1 THEN AMOUNT END) "JAN"
--CALCULATE PERCENTAGE FOR JAN (AMOUNT ItemType/AMOUNT ALL ItemType) "JAN %"
    SUM(CASE WHEN MONTH = 2 THEN AMOUNT END) "FEB"
--CALCULATE PERCENTAGE FOR FEB(AMOUNT ItemType/AMOUNT ALL ItemType) "FEB %"
...
FROM (SELECT PRICE, MONTH, ItemTYPE FROM MY TABLE)
GROUP BY typeItem

OUTPUT:
ItemType  JAN  JAN%        FEB     FEB%  ...

A         123   0.11       456     0.15
B         234   0.13       567     0.16
...

Upvotes: 1

Views: 53

Answers (2)

Popeye
Popeye

Reputation: 35910

You can achieve it using analytical function as following:

SELECT
    SUM(CASE WHEN MONTH = 1 THEN AMOUNT END) AS JAN,
    SUM(CASE WHEN MONTH = 1 THEN AMOUNT END) / MAX(CASE WHEN MONTH = 1 THEN SM END) AS "JAN%",
    SUM(CASE WHEN MONTH = 2 THEN AMOUNT END) AS FEB,
    SUM(CASE WHEN MONTH = 2 THEN AMOUNT END) / MAX(CASE WHEN MONTH = 2 THEN SM END) AS "FEB%"
...
FROM (SELECT AMOUNT, MONTH, ItemTYPE,
SUM(AMOUNT) OVER (PARTITION BY MONTH) AS SM
FROM MY TABLE)
GROUP BY typeItem

Note: you need to handle divide by zero scenario in case sum of amount in the month is zero.

Cheers!!

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270081

You can just divide:

SELECT SUM(CASE WHEN MONTH = 1 THEN AMOUNT END) as "JAN",
       SUM(CASE WHEN MONTH = 1 THEN AMOUNT END) / SUM(SUM(CASE WHEN MONTH = 1 THEN AMOUNT END)) OVER () as "JAN",       
       SUM(CASE WHEN MONTH = 2 THEN AMOUNT END) as "FEB",
       SUM(CASE WHEN MONTH = 2 THEN AMOUNT END) / SUM(SUM(CASE WHEN MONTH = 2 THEN AMOUNT END)) OVER () as "FEB",
       . . .
FROM T
WHERE MONTH IN (1, 2, . . . )
GROUP BY typeItem

Upvotes: 0

Related Questions