MySQL SUM of multiple rows from multiple table

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:

1. SUM of quotation_item_amount and SUM of invoice_item_amount PER client_project_id
2. 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.

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.