Skip to content
Advertisement

SQL query to get Net Salse by every month

I’m looking for a query to get monthly net sales I tried this far but I couldn’t get what I want. this is my Order Table

This is my CustomerReturn Table

the formula is (total of the monthly bill(Order.netAmount) – a total of monthly return (CustomerReturn.totalAmount))

in need to get net sales every year of every month.

when I run this query it shows me this

but it should be Like this

please help me. Thank you.!

Advertisement

Answer

Your query is good, it is fetching all records when there is a match on OrderId in the table CustomerReturn and doing the sums as you requested, however there are no returns for the order INV-0003, so this condition o.orderID=r.orderID is not valid when it comes to that record and it is ignoring that data. Doing a left join will fix the issue.

A left join will cause cr.totalAmount to have null values in case there is no match for o.orderID=r.orderID then we use this part; case when cr.totalAmount is null then 0 else cr.totalAmount end to fix that null issue.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement