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?
Best Answer
By the addition join on payments, of which there are two, the productAmount got doubled:
Also replaced
LEFT JOIN
withJOIN
as you don't care about non-matching entries.