MySQL SUM() of two columns from join two tables

Question

I have two tables that I need to join and get SUM() of one column in each of the table.

    mysql> select * from day_sales2;
    +--------+------------+
    | id     | ds_oversht |
    +--------+------------+
    | 119263 |      -0.17 |
    | 119336 |       0.55 |
    | 119409 |       3.08 |
    | 119482 |     -33.25 |
    | 119555 |       1.27 |
    | 119628 |       0.32 |
    | 119701 |      -0.15 |
    | 119774 |       6.57 |
    | 119847 |      -0.06 |
    | 119920 |     -12.04 |
    | 119993 |     -12.40 |
    | 120066 |     -38.90 |
    | 120139 |      20.56 |
    | 120212 |     -17.11 |
    | 120285 |      -0.51 |
    | 120358 |       0.04 |
    | 120431 |       3.23 |
    | 120504 |      -0.89 |
    | 120577 |       2.13 |
    | 120650 |       0.57 |
    | 120723 |      -1.15 |
    | 120796 |       1.99 |
    | 120869 |       9.29 |
    | 120942 |       6.95 |
    | 121015 |       3.22 |
    | 121088 |      -0.59 |
    | 121161 |      -0.89 |
    | 121234 |      -1.00 |
    +--------+------------+

    mysql> select * from over_short_adj2;
    +-------+---------+-----------+
    | id    | main_id | cd_amount |
    +-------+---------+-----------+
    | 45881 |  119920 |      0.66 |
    | 45882 |  119920 |      1.19 |
    | 45907 |  119920 |      4.81 |
    | 46017 |  120212 |      3.35 |
    | 46018 |  120066 |     16.85 |
    +-------+---------+-----------+

I'm using this Query to get that done:

    SELECT SUM(t1.ds_oversht) sum1, SUM(t2.cd_amount) sum2
    FROM day_sales2 t1 
    LEFT JOIN over_short_adj2 t2 ON t1.id = t2.main_id

But the sum1 is incorrect because join is one to many. How could I get this query work?

+--------+-------+ | sum1 | sum2 | +--------+-------+ | -83.42 | 26.86 | +--------+-------+ sum1 is -83.42 but it should be -59.34 — xfloys2112
– xfloys2112, Commented Dec 29, 2015 at 3:35

Mark · Accepted Answer · 2015-12-29 03:47:10Z

1

if you want to only get one row of sums from two tables, you could use UNION

SELECT SUM(ds_oversht) from day_sales2
UNION
SELECT SUM(cd_amount) from over_short_adj2;

answered Dec 29, 2015 at 3:47

Mark

2,0433 gold badges22 silver badges43 bronze badges

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

1 Comment

xfloys2112 Over a year ago

hmm, or will it, interesting

Felix Pamittan · Accepted Answer · 2015-12-29 03:54:34Z

1

You should get the SUM of day_sales2 first before doing a LEFT JOIN:

SELECT
    t1.id,
    t1.sum1, 
    SUM(t2.cd_amount) AS sum2
FROM (
    SELECT id, SUM(ds_oversht) AS sum1
    FROM day_sales2
    GROUP BY id
) t1
LEFT JOIN over_short_adj2 t2 
    ON t1.id = t2.main_id
GROUP BY t1.id, t1.sum1

EDIT:

If you only want one row, I think the JOIN is not needed:

SELECT
    (SELECT SUM(ds_oversht) FROM day_sales2),
    (SELECT SUM(cd_amount) FROM over_short_adj2)

edited Dec 29, 2015 at 3:54

answered Dec 29, 2015 at 3:35

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

1 Comment

xfloys2112 Over a year ago

Query should only return one row of sums.

xfloys2112 · Accepted Answer · 2015-12-29 03:57:36Z

0

    SELECT SUM(sum1) sum1, SUM(sum2) sum2 FROM (
    SELECT
        t1.id,
        t1.sum1, 
        SUM(t2.cd_amount) sum2
    FROM (
        SELECT id, SUM(ds_oversht) sum1
        FROM day_sales2
        GROUP BY id
    ) t1
    LEFT JOIN over_short_adj2 t2 
        ON t1.id = t2.main_id
    GROUP BY t1.id, t1.sum1
    ) t3

This does it, thanks Felix for the answer.

answered Dec 29, 2015 at 3:57

xfloys2112

7131 gold badge5 silver badges14 bronze badges

3 Comments

Felix Pamittan Over a year ago

See my edited answer. It's more efficient and less complex.

xfloys2112 Over a year ago

but the Join is needed because there is more to that query

xfloys2112 Over a year ago

SELECT ds_per, SUM(sum1) sum1, SUM(sum2) sum2 FROM ( 		SELECT 			t1.id,             t1.ds_per,              t1.ds_sto, 			t1.sum1,  			SUM(t2.cd_amount) sum2 		FROM ( 			SELECT id, ds_per, ds_sto, ds_date, SUM(ds_oversht) sum1 			FROM day_sales 			GROUP BY id 		) t1 		LEFT JOIN over_short_adj t2  			ON t1.id = t2.main_id 			WHERE t1.ds_sto = 6 AND t1.ds_date BETWEEN '2014-12-29' AND '2015-12-27' 		GROUP BY t1.id, t1.sum1 		) t3 GROUP BY ds_sto, ds_per

This is the full query, works great

Collectives™ on Stack Overflow

MySQL SUM() of two columns from join two tables

3 Answers 3

1 Comment

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related