SQL query to remove reversed charges

I’m trying to solve a chargeback issue. Sample data provided below. I need to completely remove the charges that have been chargeback (UnitSold -1) from the results. The highlighted rows are all that should remain. I have tried using COUNT and SUM to try to get it to zero out or give me at least enough code to rule out what shouldn’t be there, but have just ended up banging my head against the wall.

TABLE DATA
|OrderDate |CustomerID|ProductID|UnitsSold|TotalCharge|
|----------|----------|---------|---------|-----------|
| 1/14/2020|  12345   |  99496  |    -1   |  730.00   |
| 1/14/2020|  12345   |  99496  |     1   |  730.00   |
| 2/27/2020|  67890   |  99496  |     1   |  160.00   |
| 2/27/2020|  67890   |  99496  |     1   |  210.00   |
| 2/27/2020|  67890   |  99496  |    -1   |  210.00   |
|10/22/2020|  98754   |  99496  |     1   |  550.00   |
|----------|----------|---------|---------|-----------|


FINAL RESULTS
|OrderDate |CustomerID|ProductID|UnitsSold|TotalCharge|
|----------|----------|---------|---------|-----------|
| 2/27/2020|  67890   |  99496  |     1   |  160.00   |
|10/22/2020|  98754   |  99496  |     1   |  550.00   |
|----------|----------|---------|---------|-----------|

Answer

You can try the below –

select date,customer_id,product_id,sum(unit_sold) as unit_solde,sum(unit_sold*unit_charge) as unit_charge 
from table
group by date,customer_id,product_id
having sum(unit_sold)>=1

Advertisement

Answer