I have orders table with two columns sales_amt, Disc_Amt. I want to find the each month total sales amount having discounts and without discount

Table:

Result required:

I tried with the below query

Select 
         month(invoice_date),
         sales_amt as Sales_Amount_Without_Discount,
         Sales_Amount_Without_Discount =  (select  
                                                 sales_amt 
                                           from 
                                                 orders
                                           where
                                                 disc_amt > '0'  
                                           )
from
       orders
Group by 
       month(invoice_date)

Currently I am getting result as below

Thank you

CodePudding user response：

You can do this by putting a case expression within your SUM(), e.g.

SELECT  MONTH(invoice_date),
        Sales_Amount_Without_Discount = SUM(sales_amt),
        Sales_Amount_With_Discount = SUM(CASE WHEN Disc_Amt > 0 THEN sales_amt ELSE 0 END)
FROM    orders
GROUP BY
        MONTH(invoice_date);