0

There are structures:

CREATE TABLE `invoices` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `invoices` VALUES (1,'2018-09-22');

CREATE TABLE `products` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`invoice_id` int(10) unsigned NOT NULL,
`amount` decimal(10,2) unsigned NOT NULL,
`quantity` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `products` VALUES (1,1,150.00,2),(2,1,60.00,3),(3,1,50.00,1);

CREATE TABLE `payments` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`invoice_id` int(10) unsigned NOT NULL,
`amount` decimal(10,2) unsigned NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `payments` VALUES (1,1,400.00,'2018-09-23'),(2,1,80.00,'2018-09-23');

I have this query:

select i.id, sum(pr.amount * pr.quantity) as productAmount, 
sum(pm.amount) as paymentAmount
from invoices as i
left join products as pr on pr.invoice_id=i.id
left join payments as pm on pm.invoice_id=i.id
group by i.id

and have this result:

+----+---------------+---------------+
| id | productAmount | paymentAmount |
+----+---------------+---------------+
|  1 |       1060.00 |       1440.00 |
+----+---------------+---------------+
1 row in set (0,00 sec)

However, I want to get the following result:

+----+---------------+---------------+
| id | productAmount | paymentAmount |
+----+---------------+---------------+
|  1 |        530.00 |        480.00 |
+----+---------------+---------------+
1 row in set (0,00 sec)

I want sum amount of products and sum amount of payments grouped by invoice.id.
What should be the query in this case?

0

By the addition join on payments, of which there are two, the productAmount got doubled:

select i.id, sum(distinct pr.amount * pr.quantity) as productAmount, 
sum(distinct pm.amount) as paymentAmount
from invoices as i
join products as pr on pr.invoice_id=i.id
join payments as pm on pm.invoice_id=i.id
group by i.id;

Also replaced LEFT JOIN with JOIN as you don't care about non-matching entries.

0

Here is another option:

SELECT ProductAmt.id
    ,productAmount
    ,paymentamt.paymentamount
FROM (
    SELECT i.id
        ,sum(pr.amount * pr.quantity) AS productAmount
    FROM invoices AS i
    LEFT JOIN products AS pr ON pr.invoice_id = i.id
    GROUP BY i.id
    ) ProductAmt
LEFT JOIN (
    SELECT i.id
        ,sum(pm.amount) AS paymentAmount
    FROM invoices AS i
    LEFT JOIN payments AS pm ON pm.invoice_id = i.id
    GROUP BY i.id
    ) PaymentAmt ON productAmt.id = paymentamt.id

SQL Fiddle example here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.