CODEWITHSUNDEEP
mysqlsqlmariadb
Accountant م
Accountant م

Reputation: 7533

How to select the sum() of a group of rows and the sum() of another group

I have created a SQLfiddle demo with sample data and desired result here :(http://sqlfiddle.com/#!9/dfe73a/7)

sample data

-- table company
+--------+---------+
|   id   |   name  |
+--------+---------+
|   1    |   foo   |
|   2    |   bar   |
+--------+---------+

 -- table sales
+--------+---------------+-----------------+
|   id   |   company_id  |   total_amount  |
+--------+---------------+-----------------+
|   1    |       1       |      300.0      |
|   2    |       1       |      300.0      |
|   2    |       1       |      100.0      |
+--------+---------------+-----------------+

 -- table moves
+--------+---------------+-----------------+
|   id   |   company_id  |   balance_move  |
+--------+---------------+-----------------+
|   1    |       1       |      700.0      |
|   2    |       1       |     -300.0      |
|   2    |       1       |     -300.0      |
+--------+---------------+-----------------+

I need to select every company along with the sum of it's total amount of sales and the sum of it's total balance moves

desired result

+----+----------------------+---------------------+
| id |  total_amount_sum    |   balance_move_sum  |
+----+----------------------+---------------------+
| 1  |         700          |          100        |
+----+----------------------+---------------------+
| 2  |        (null)        |        (null)       |
+----+----------------------+---------------------+

I tried this SQL query

SELECT 
    company.id,
    sum(total_amount) total_amount_sum,
    sum(balance_move) balance_move_sum
FROM company
     LEFT JOIN sales ON company.id = sales.company_id 
     LEFT JOIN moves ON company.id = moves.company_id
GROUP BY company.id

But the sum() functions add all the redundant values came from the joins which result in 2100 (700*3) for total amount and 300 (100*3) for net balance

bad SQL statement result

+----+----------------------+---------------------+
| id |  total_amount_sum    |   balance_move_sum  |
+----+----------------------+---------------------+
| 1  |         2100         |          300        |
+----+----------------------+---------------------+
| 2  |        (null)        |        (null)       |
+----+----------------------+---------------------+

Is it possible to achieve the result I want ?

Upvotes: 2

Views: 158

Answers (2)

Aaron Dietz
Aaron Dietz

Reputation: 10297

You're repeating rows by doing your joins.

Company: 1 row per company

After Sales join: 3 rows per company (1x3)

After Moves join: 9 rows per company (3x3)

You end up triplicating your SUM because of this.

One way to fix is to use derived tables like this, which calculate the SUM first, then join the resulting rows 1-to-1.

SELECT 
    company.id,
    total_amount_sum,
    balance_move_sum
FROM company
LEFT JOIN (SELECT SUM(total_amount) total_amount_sum, company_id
           FROM sales
           GROUP BY company_id
           ) sales ON company.id = sales.company_id 
LEFT JOIN (SELECT SUM(balance_move) balance_move_sum, company_id
           FROM moves
           GROUP BY company_id
           ) moves ON company.id = moves.company_id

Upvotes: 1

JeffUK
JeffUK

Reputation: 4251

Using sub-queries to calculate the two sums separately will work.

SELECT 
    company.id,
    (Select sum(total_amount) from sales where sales.company_id = company.id) total_amount_sum,
    (Select sum(balance_move) from moves where moves.company_id = company.id) balance_move_sum
FROM company

Upvotes: 0

Related Questions