CODEWITHSUNDEEP
mysqlsql
Lutch
Lutch

Reputation: 119

Calculation in Sum Field in SQL

I have a query which is returning me a sum group by mode of payment

select
RTRIM(shopNo) as Shop_Number,
REPLACE(CONVERT(VARCHAR(10), DateTransaction, 103), '/', '') AS Todays_Date,
CodeModeDePaiement as Mode_Of_Payment,
SUM(convert(DOUBLE PRECISION,  Amount * 100 ) * 10) As Amount
from InvoiceSettlement
where  CodeModeDePaiement in 
(101,130,132,135,104,103,124,107,
136,117,131,410,106,122,109,102,105,112,133,999)
and   DateTransaction =  '2017/12/31' 
group by ShopNo,DateTransaction,CodeModeDePaiement

The output result is a below :

Shop_Number Todays_Date Mode_Of_Payment Amount
  2         31122017    102             18421610
  2         31122017    130             2332371390
  2         31122017    132             1082810
  2         31122017    106             66457640
  2         31122017    117             23925000
  2         31122017    133             5700000
  2         31122017    999             -490653940
  2         31122017    101             2404194870

I want the result to always display Sum Amount for Mode_of_Payment(101 + 999) as 101. I need the result as below :

Shop_Number Todays_Date Mode_Of_Payment Amount
  2         31122017    102             18421610
  2         31122017    130             2332371390
  2         31122017    132             1082810
  2         31122017    106             66457640
  2         31122017    117             23925000
  2         31122017    133             5700000
  2         31122017    101             1913540930

Upvotes: 1

Views: 57

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Use a CASE expression to map 101 and 999 to the same thing:

SELECT
    RTRIM(shopNo) AS Shop_Number,
    REPLACE(CONVERT(VARCHAR(10), DateTransaction, 103), '/', '') AS Todays_Date,
    CASE WHEN CodeModeDePaiement IN (101, 999)
         THEN 101
         ELSE CodeModeDePaiement END as Mode_Of_Payment,
    SUM(CONVERT(DOUBLE PRECISION,  Amount * 100 ) * 10) AS Amount
FROM InvoiceSettlement
WHERE CodeModeDePaiement IN (101, 130, 132, 135, 104, 103, 124, 107, 136, 117,
    131, 410, 106, 122, 109, 102, 105, 112, 133, 999) AND
    DateTransaction = '2017/12/31' 
GROUP BY
    ShopNo,
    DateTransaction,
    CASE WHEN CodeModeDePaiement IN (101, 999)
         THEN 101
         ELSE CodeModeDePaiement END;

Upvotes: 2

Related Questions