CODEWITHSUNDEEP
mysql
Addy
Addy

Reputation: 41

Sum of Pending Amounts Based on Customer with each line on display

I am looking to display the details of all pending invoices along with a column which shows the total of the pending invoices of a particular customer but I am unable to find a way to do the same. Here is what I am trying to do:

+----------+------------+---------------+---------------+
| Customer | Invoice No | Peding Amount | Total Pending |
+----------+------------+---------------+---------------+
| A        |          1 |            10 |          1110 |
| B        |          2 |           100 |           110 |
| C        |          3 |          1000 |          3020 |
| A        |          4 |          1000 |          1110 |
| A        |          5 |           100 |          1110 |
| B        |          6 |            10 |           110 |
| C        |          7 |          2000 |          3020 |
| C        |          8 |            20 |          3020 |
+----------+------------+---------------+---------------+

Now I would like to inform that the table only has first 3 columns but I need to add this 4th column but I am unable to find a way to have sum based on the customer.

Here is the code I am trying to use but I am getting some sort of syntax.

select 
 `tabSales Invoice`.`posting_date` as "Invoice Date:Date:80",
 `tabSales Invoice`.`due_date` as "Due Date:Date:80",
 `tabSales Invoice`.`name` as "Invoice No:Link/Sales Invoice:120",
 `tabSales Invoice`.`customer` as "Customer:Link/Customer:180",
 `tabSales Invoice`.`grand_total` as "Total:Currency:140",
 `tabSales Invoice`.`outstanding_amount` as "Pending:Currency:140",
 datediff(curdate(),`tabSales Invoice`.`posting_date`) as "Over By Invoice Date:Float:80",
 datediff(curdate(),`tabSales Invoice`.`due_date`) as "Over By Due Date:Float:80",
 `tabSales Invoice`.`debit_to` as "Customer Account:Link/Account:200"
from
 `tabSales Invoice`
where
 `tabSales Invoice`.`docstatus` = 1
 and `tabSales Invoice`.`outstanding_amount` > 0.005


Inner join(
    Select 
    `tabSales Invoice`.`customer`,
    SUM(`tabSales Invoice`.`outstanding_amount`) AS "Total Pending::180"
    from
     `tabSales Invoice`
     Group By
      `tabSales Invoice`.`customer`) 
     `tabSales Invoice` ON `tabSales Invoice`.`customer`

Upvotes: 0

Views: 183

Answers (2)

Strawberry
Strawberry

Reputation: 33945

Consider this example...

 DROP TABLE IF EXISTS my_table;

 CREATE TABLE my_table
 (Customer CHAR(1) NOT NULL
 ,Invoice_No INT NOT NULL
 ,Pending_Amount INT NOT NULL
 ,PRIMARY KEY(Customer,Invoice_No)
 );

 INSERT INTO my_table VALUES
 ('A',1,10),
 ('B',2,100),
 ('C',3,1000,
 ('A',4,1000),
 ('A',5,100),
 ('B',6,10),
 ('C',7,2000),
 ('C',8,20);

 SELECT x.*
      , SUM(y.Pending_Amount) Total_Pending 
   FROM my_table x 
   JOIN my_table y 
     ON y.customer = x.customer 
  GROUP 
     BY x.customer
      , x.invoice_no;
 +----------+------------+----------------+---------------+
 | Customer | Invoice_No | Pending_Amount | Total_Pending |
 +----------+------------+----------------+---------------+
 | A        |          1 |             10 |          1110 |
 | A        |          4 |           1000 |          1110 |
 | A        |          5 |            100 |          1110 |
 | B        |          2 |            100 |           110 |
 | B        |          6 |             10 |           110 |
 | C        |          3 |           1000 |          3020 |
 | C        |          7 |           2000 |          3020 |
 | C        |          8 |             20 |          3020 |
 +----------+------------+----------------+---------------+

Upvotes: 1

Matt Busche
Matt Busche

Reputation: 14333

You can use a subQuery

SELECT customer, invoiceno, pendingamount, pending
FROM mytable 
    INNER JOIN (
    SELECT customer, SUM(pendingamount) AS pending
    FROM mytable
    GROUP BY customer) a on mytable.customer ON a.customer

Upvotes: 0

Related Questions