CODEWITHSUNDEEP
sqloracle-databasejoingroup-byaggregate-functions
Imran Hemani
Imran Hemani

Reputation: 629

How to use two aggregations from one table to calculate a metric in oracle SQL

I have a table named TRANSACTION, that has the following columns:

ITEM
STORE
REASON
QUANTITY

I need to calculate a metric, "30_DAYS_SALES_QUANTITY" as per the following calculation:-

30_DAYS_SALES_QUANTITY = (Aggregated SUM of QUANTITY by ITEM, STORE when REASON = 183) - (Aggregated SUM of QUANTITY by ITEM, STORE when REASON = 182) BETWEEN SYSDATE - 30 and SYSDATE.

Upvotes: 1

Views: 52

Answers (1)

valex
valex

Reputation: 24144

You should use CASE statement

SELECT ITEM, STORE,
    SUM(CASE WHEN REASON = 183 THEN QUANTITY ELSE 0 END)-
    SUM(CASE WHEN REASON = 182 THEN QUANTITY ELSE 0 END)
FROM TRANSACTION
GROUP BY ITEM, STORE

Upvotes: 1

Related Questions