MySQL SUM of multiple rows from multiple table

Question

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.

if you would include the scripts to create your tables and populate them with data, or even better if you could do that on some site like sqlfiddle.com or db-fiddle.com and then post the link in your question it would be a lot easier for others to help you get an answer to your question. also, if you could provide an example of the result set your current query is giving you as well as an example of how it should look being sure to clearly point out the differences between the two that would help a lot as well — Gharbad The Weak
– Gharbad The Weak, Commented Dec 27, 2020 at 7:12

Gordon Linoff · Accepted Answer · 2020-12-27 14:34:26Z

1

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.

edited Dec 27, 2020 at 14:34

answered Dec 27, 2020 at 13:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Charas Over a year ago

Thanks for the suggestion, however this return error: Unknown column 'qi.quotation_id' in 'on clause', I'm guessing it's because of the second part?

Charas Over a year ago

And for the SELECT * is just for example, at real time I'm specifically selecting each column

Charas Over a year ago

I have edited my question to specifically 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

Gordon Linoff Over a year ago

@Charas . . . I fixed the ON clauses.

Charas Over a year ago

Thanks man! It really works as intended, however when I added WHERE i.inv_amt > q.quot_amt at the end, it does not return the correct data, or did I put it at the wrong position?

|

Jaragoth · Accepted Answer · 2020-12-27 07:27:13Z

0

I may be missing something, but your table descriptions do not include a example for data_client or data_client_project Given your example, I expect your row expansion is coming from the first 3 joins.

Make sure that the below is giving you the list of data you want first, then try joining in the calculation:

SELECT *
    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;
#you may want to append the above with a limit 100 for testing.

answered Dec 27, 2020 at 7:27

Jaragoth

587 bronze badges

1 Comment

Jaragoth Over a year ago

Note: the above is not a complete answer. But trying to help with the information given. As Gharbad The Weak pointed out example data added in something like db-fiddle.com would do wonders.

ScaisEdge · Accepted Answer · 2020-12-27 08:02:17Z

if you have duplicated rows form the main query then add distinct for obatin a only distinct rows and andd the where conditio for filtering the result by quotitem.quot_amt < invitem.inv_amt

    SELECT distinct a.*, b.*, d.*, i.*  
    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 * c.quotation_item_qty) 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
    WHERE quotitem.quot_amt < invitem.inv_amt

Collectives™ on Stack Overflow

MySQL SUM of multiple rows from multiple table

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related