CODEWITHSUNDEEP
sqloracle-databasesumcase-whensubtraction
BigRedEO
BigRedEO

Reputation: 847

SUM and MINUS in one SQL query parameter

I have a working Oracle SQL query to which I must make a change, but not sure how to go about it. Query below:

SELECT
  USERID,
  SUM(CONTAINERS),
  SUM(QTYMISTINT),
  SUM(QTYXMISTINT),
  SUM(CASE WHEN (BOOKDATE >= '01-JAN-18' AND BOOKDATE <= '31-DEC-18')
           THEN GALLONS ELSE 0 END) AS Dispensed2018,
  SUM(CASE WHEN (BOOKDATE >= '01-JAN-18' AND BOOKDATE <= '31-DEC-18')
           THEN GALSMISTINT ELSE 0 END) AS Mistints2018
FROM MQ_CDS_NETTRAN 
WHERE STORENBR = 1564
  AND TRANCODE IN ('DISP','MIST')
GROUP BY USERID
ORDER BY USERID;

What I need to do is regarding the parameter 

(BOOKDATE >= '01-JAN-18' AND BOOKDATE <= '31-DEC-18')
THEN GALSMISTINT ELSE 0 END) AS Mistints2018

I need to subtract a quantity from that field: I need to do a SUM on another field GALSXMISTINT and then subtract GALSXMISTINT from Mistints2018 to get final result called NetGalsMistint2018. How would I do that?

Upvotes: 0

Views: 180

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522181

You might be able to take a conditional sum of the difference:

SELECT
    USERID,
    SUM(CONTAINERS),
    SUM(QTYMISTINT),
    SUM(QTYXMISTINT),
    SUM(CASE WHEN BOOKDATE BETWEEN '01-JAN-18' AND '31-DEC-18'
             THEN GALLONS ELSE 0 END) AS Dispensed2018,
    SUM(CASE WHEN BOOKDATE BETWEEN '01-JAN-18' AND '31-DEC-18'
             THEN GALSMISTINT ELSE 0 END) AS Mistints2018,
    SUM(CASE WHEN BOOKDATE BETWEEN '01-JAN-18' AND '31-DEC-18'
             THEN GALSMISTINT - GALSXMISTINT ELSE 0 END) AS NetGalsMistint2018
FROM MQ_CDS_NETTRAN 
WHERE
    STORENBR = 1564 AND TRANCODE IN ('DISP','MIST')
GROUP BY
    USERID
ORDER BY
    USERID;

Upvotes: 2

Hogan
Hogan

Reputation: 70538

Not sure of your exact requirements but here is an example of left joining to a sub-query of the sums of a column.

SELECT
  USERID,
  SUM(CONTAINERS),
  SUM(QTYMISTINT),
  SUM(QTYXMISTINT),
  SUM(CASE WHEN (BOOKDATE >= '01-JAN-18' AND BOOKDATE <= '31-DEC-18')
           THEN GALLONS ELSE 0 END) AS Dispensed2018,
  SUM(CASE WHEN (BOOKDATE >= '01-JAN-18' AND BOOKDATE <= '31-DEC-18')
           THEN GALSMISTINT ELSE 0 END) AS Mistints2018,
  SUM(CASE WHEN (BOOKDATE >= '01-JAN-18' AND BOOKDATE <= '31-DEC-18')
           THEN GALSMISTINT ELSE 0 END) - x.SUM_GAL AS NetGalsMistint2018
FROM MQ_CDS_NETTRAN 
LEFT JOIN ( SELECT USERID, SUM(GALSXMISTINT) AS SUM_GAL 
            FROM MQ_CDS_NETTRAN
            GROUP BY USERID
) x ON X.USERID = MQ_CDS_NETTRAN.USERID
WHERE STORENBR = 1564
  AND TRANCODE IN ('DISP','MIST')
GROUP BY USERID
ORDER BY USERID;

Upvotes: 1

Related Questions