Question 21940 in Mysql Question posted in
The official MySQL documentation can be found here.

Delete Lines from table if the sum of multiple lines is Nil (based on criteria) – Mysql

I’m working with the following table where you can get activity from customer purchases.

DateOfActivity CustomerReference Reference Line Description Receivable Amount
24/10/2022 CUST567 1 Credit Purchase 20,000
24/10/2022 CUST567 4 Credit Purchase 10,000
24/10/2022 CUST555 2 Credit Purchase 50,000
27/10/2022 CUST555 2 Contract Sign 0
27/10/2022 CUST567 4 Contract Sign 0
27/10/2022 CUST567 1 Contract Sign 0
27/10/2022 CUST567 4 Repayment -3,500
27/10/2022 CUST567 4 Repayment -6,500
13/11/2022 CUST567 1 Repayment -10,000
13/11/2022 CUST567 1 Repayment -2,000
18/11/2022 CUST567 1 Contract Sign 0
18/11/2022 CUST567 1 Repayment -3,000

I’m using the following query to extract the above table:

Select
DateOfActivity, CustomerReferencce, ReferenceLine, Description, ReceivableAmount
From 'Table A'
Where
DateOfActivity >= '2022-09-01'
Group by
DateOfActivity

As you can see that the table will only get bigger because more customer activity is being added. How can I change my query so the customers who have fully paid their receivable amount don’t show up in this table?

The result from the above script change that I am expecting is as follows:

DateOfActivity CustomerReference Reference Line Description Receivable Amount
24/10/2022 CUST567 1 Credit Purchase 20,000
24/10/2022 CUST555 2 Credit Purchase 50,000
27/10/2022 CUST555 2 Contract Sign 0
27/10/2022 CUST567 1 Contract Sign 0
13/11/2022 CUST567 1 Repayment -10,000
13/11/2022 CUST567 1 Repayment -2,000
18/11/2022 CUST567 1 Contract Sign 0
18/11/2022 CUST567 1 Repayment -3,000

CUST567 Reference Line 4 has been removed because the sum of his Credit Purchase + Contract Sign + Repayment = $0. All other Customer rows are still showing up.

How can edit the query so this is done automatically for Large data? Please note the following assumptions:

  • Customer Reference for multiple customers can be the same or different (for example in the above example, CUST567 has two Reference Lines 1 & 4. However, CUST555 only has one reference Line 2.

  • The data is removed for Customers based on the Receivable amount coming down to Nil (so all rows for that CustomerReference & Reference Line are removed)

Thanks in Advance

Tags:

2

Answers


  1. 0

    What are you trying to achieve here? Are the references to large data because something is getting slow or something?

    I’d suggest you’d want to make a view for your query (google the syntax for your flavour of SQL, but should be something like create view as {your query};)

    Then you can query that view with an additional where "Receivable Amount !=0

    You could also make a subquery, i.e select * from ({your query}) as x where x."Receivable Amount !=0

    But if you’ve written the sql you’ve gotten to so far I assume you know that? So is there an issue in terms of performance or some such that’s caused you to ask the question?

    Login or Signup to reply.
  2. 0

    As I understand the question, we need to build the sum of the amounts grouped by the reference line. So this query will get those reference lines having this sum of amounts = 0:

    SELECT 
ReferenceLine
FROM tableA
GROUP BY ReferenceLine
HAVING SUM(ReceivableAmount) = 0;

    Then, we can use this query as a subquery and fetch all other entries not having such a reference line:

    SELECT 
DateOfActivity, CustomerReference, ReferenceLine,
Description, ReceivableAmount
FROM tableA
WHERE ReferenceLine NOT IN 
(SELECT 
ReferenceLine
FROM tableA
GROUP BY ReferenceLine
HAVING SUM(ReceivableAmount) = 0);

    This will produce the expected outcome.

    We could of course also use IN and amount <> 0 instead of NOT IN and amount = 0:

    SELECT 
DateOfActivity, CustomerReference, ReferenceLine,
Description, ReceivableAmount
FROM tableA
WHERE ReferenceLine IN 
(SELECT 
ReferenceLine
FROM tableA
GROUP BY ReferenceLine
HAVING SUM(ReceivableAmount) <> 0);

    This will create the same result. Just take what you prefer.

    Try out: db<>fiddle

    An important note: In your question and comments, you are talking about the sum for the reference line is "nil" or "null". This is untrue. It is zero.

    NULL or NIL would mean there is no amount, so this is something completely different!

    All your amounts are NOT NULL and the sum of them is also NOT NULL, but zero for reference line 4.

    EDIT: If it should also be grouped by CustomerReference, we can extend the previous queries like this:

    SELECT 
DateOfActivity, CustomerReference, ReferenceLine,
Description, ReceivableAmount
FROM tableA
WHERE (CustomerReference, ReferenceLine) NOT IN 
(SELECT 
CustomerReference, ReferenceLine
FROM tableA
GROUP BY CustomerReference, ReferenceLine
HAVING SUM(ReceivableAmount) = 0);

    OR

    SELECT 
DateOfActivity, CustomerReference, ReferenceLine,
Description, ReceivableAmount
FROM tableA
WHERE (CustomerReference, ReferenceLine) IN 
(SELECT 
CustomerReference, ReferenceLine
FROM tableA
GROUP BY CustomerReference, ReferenceLine
HAVING SUM(ReceivableAmount) <> 0);

    Updated fiddle: db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search