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

+----------+-----------+--------+------------+---------------+-------------+-----------+---------+---------+
| orderID  | custID    | userID | orderDate  | paymentMethod | grossAmount | netAmount | cash    | balance |
+----------+-----------+--------+------------+---------------+-------------+-----------+---------+---------+
| INV-0001 | CUST-0001 | U-001  | 2020-05-01 | Cash Pay      |      525.00 |    525.00 |  550.00 |   25.00 |
| INV-0002 | CUST-0001 | U-001  | 2020-05-01 | Cash Pay      |      240.00 |    240.00 |  250.00 |   10.00 |
| INV-0003 | CUST-0001 | U-001  | 2020-05-01 | Cash Pay      |      220.00 |    220.00 |  250.00 |   30.00 |
| INV-0004 | CUST-0001 | U-001  | 2020-04-30 | Cash Pay      |      895.00 |    895.00 | 1000.00 |  105.00 |
| INV-0005 | CUST-0001 | U-001  | 2020-04-30 | Cash Pay      |      300.00 |    300.00 |  500.00 |  200.00 |
| INV-0006 | CUST-0001 | U-001  | 2020-04-30 | Cash Pay      |      230.00 |    230.00 |  250.00 |   20.00 |
+----------+-----------+--------+------------+---------------+-------------+-----------+---------+---------+

This is my CustomerReturn Table

+-------+----------+------------+--------+------------+-----------+-----------+-------------+
| retID | orderID  | itemCode   | userID | retDate    | returnQty | unitPrice | totalAmount |
+-------+----------+------------+--------+------------+-----------+-----------+-------------+
|     1 | INV-0001 | 1800232050 | U-001  | 2020-05-01 |      1.00 |    100.00 |      100.00 |
|     2 | INV-0002 | 1909873674 | U-001  | 2020-05-01 |      2.00 |     55.00 |      110.00 |
|     3 | INV-0004 | 1800232050 | U-001  | 2020-04-30 |      1.00 |    100.00 |      100.00 |
+-------+----------+------------+--------+------------+-----------+-----------+-------------+

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.

select orderDate,sum(netAmount)-sum(totalAmount) from `Order` o,CustomerReturn r where o.orderID=r.orderID GROUP BY orderDate;

when I run this query it shows me this

+------------+---------------------------------+
| orderDate  | sum(netAmount)-sum(totalAmount) |
+------------+---------------------------------+
| 2020-04-30 |                          795.00 |
| 2020-05-01 |                          555.00 |
+------------+---------------------------------+

but it should be Like this

+------------+---------------------------------+
| orderDate  | sum(netAmount)-sum(totalAmount) |
+------------+---------------------------------+
| 2020-04-30 |                         1425.00 |
| 2020-05-01 |                          775.00 |
+------------+---------------------------------+

please help me. Thank you.!

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.

select 
    o.orderDate, 
    sum(o.netAmount)-sum(case when cr.totalAmount is null then 0 else cr.totalAmount end)
from 
    Orders o 
left join 
    CustomerReturn cr
on 
    o.orderID = cr.orderID
group by 
    o.orderDate

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.

Advertisement

Answer