I am trying to get the sum of multiple rows from 2 different tables, but somehow the result returns multiple rows.
I need to get the SUM of quotation_item_amount (group by quotation_id) and invoice_item_amount (group by invoice_id) and if I query unpaid quotation, I need to get WHERE SUM(invoice) < SUM(quotation)
So here’s my sample table
table client_project_id +-------------------+-----------+----------------------+ | client_project_id | client_id | client_project_title | +-------------------+-----------+----------------------+ | 23 | 5 | Project 1 | | 17 | 9 | Project 2 | | 54 | 7 | Project 3 | +-------------------+-----------+----------------------+ table quotation +--------------+-------------------+------------------+ | quotation_id | client_project_id | quotation_number | +--------------+-------------------+------------------+ | 1 | 23 | Q/01/2020/001 | | 2 | 17 | Q/01/2020/002 | | 3 | 54 | Q/01/2020/003 | +--------------+-------------------+------------------+ table quotation_item +-------------------+--------------+-----------------------+ | quotation_item_id | quotation_id | quotation_item_amount | +-------------------+--------------+-----------------------+ | 1 | 1 | 500 | | 2 | 1 | 700 | | 3 | 1 | 600 | | 4 | 2 | 200 | | 5 | 2 | 150 | | 6 | 3 | 900 | +-------------------+--------------+-----------------------+ table invoice +--------------+-------------------+------------------+ | invoice_id | client_project_id | invoice_number | +--------------+-------------------+------------------+ | 1 | 23 | I/01/2020/001 | | 2 | 17 | I/01/2020/002 | | 3 | 54 | I/01/2020/003 | +--------------+-------------------+------------------+ table invoice_item +-------------------+--------------+-----------------------+ | invoice_item_id | invoice_id | invoice_item_amount | +-------------------+--------------+-----------------------+ | 1 | 1 | 500 | | 2 | 1 | 700 | | 3 | 1 | 600 | | 4 | 2 | 200 | | 5 | 2 | 150 | | 6 | 3 | 900 | +-------------------+--------------+-----------------------+
The result that I need to obtain is:
- SUM of quotation_item_amount and SUM of invoice_item_amount PER client_project_id
- To query WHERE SUM(invoice) < SUM(quotation)
Here is my latest try at the query
SELECT SUM(quotation_item.quotation_item_amount) as quot_amt, SUM(invoice_item.invoice_item_amount) as inv_amt, data_client_project.client_project_id, data_client.client_name FROM data_client_project a LEFT JOIN quotation b ON a.client_project_id = b.client_project_id LEFT JOIN data_client d ON a.client_id = d.client_id LEFT JOIN invoice i ON a.client_project_id = i.client_project_id JOIN ( SELECT quotation_id, SUM(c.quotation_item_amount) as quot_amt FROM quotation_item c GROUP BY c.quotation_id ) quotitem ON b.quotation_id = quotitem.quotation_id JOIN ( SELECT invoice_id, SUM(e.invoice_item_price) as inv_amt FROM invoice_item e GROUP BY e.invoice_id ) invitem ON i.invoice_id = invitem.invoice_id
However, this results in multiple duplicate rows of the quotation_item_amount and invoice_item_amount.
Have tried using UNION / UNION ALL and several other queries which just do not work. Thank you for all your suggestions.
Advertisement
Answer
It looks like you are trying to aggregate along two different dimensions at the same time. The solution is to pre-aggregate along each dimension:
SELECT * FROM data_client_project cp LEFT JOIN (SELECT q.client_project_id, SUM(qi.quotation_item_amount * qi.quotation_item_qty) as quot_amt FROM quotation q JOIN quotation_item qi ON qi.quotation_id = q.quotation_id GROUP BY q.client_project_id ) q USING (client_project_id) LEFT JOIN (SELECT i.client_project_id, SUM(invoice_item_price) as inv_amt FROM invoice i JOIN invoice_item ii ON i.invoice_id = ii.invoice_id GROUP BY i.client_project_id ) i USING (client_project_id);
Two notes about your style.
First, you are using arbitrary letters for table aliases. This makes the query quite hard to follow and becomes quite awkward if you add new tables, remove tables, or rearrange the names. Use abbreviations for the tables. Much easier to follow.
Second, I don’t really recommend SELECT *
for such queries. But, you can avoid duplicated column by replacing ON
with USING
.