CODEWITHSUNDEEP
mysql
Dalbin Shimy
Dalbin Shimy

Reputation: 53

mysql sum and count 2 tables group by issue

Table A

id  | s_id      | unit_value
-----------------------------
1   | S01       | 5
2   | S01       | 8
3   | S01       | 6
4   | S01       | 3
5   | S02       | 9

Table B

id  | a_id      | price
------------------------
1   | 2         | 900
2   | 2         | 100
3   | 2         | 600
4   | 3         | 100
5   | 5         | 100
6   | 1         | 10
7   | 1         | 200
8   | 4         | 100
9   | 4         | 200

need result as

s_id    |  total_price  | total_unit  | count(s_id)
----------------------------------------------------
SO1     |    2210       |      22     |   4
SO2     |    100        |      9      |   1

For this I have tried query as

SELECT a.s_id
     , SUM(b.price) AS total_price
     , SUM(a.unit_value) AS total_unit
     , COUNT(s_id)
  FROM A a
  JOIN B b 
    ON b.a_id = a.id 
 GROUP 
    BY a.s_id

Can anyone help me how to achieve the correct result

Upvotes: 1

Views: 46

Answers (2)

nbk
nbk

Reputation: 49375

Simply Add the prices up in a subquery for B

SELECT a.s_id
     , SUM(b.price) AS total_price
     , SUM(a.unit_value) AS total_unit
     , COUNT(s_id)
  FROM A a
  JOIN (SELECT SUM(price) price , a_id FROM B GROUP BY a_id) b  
    ON b.a_id = a.id 
 GROUP 
    BY a.s_id

s_id | total_price | total_unit | COUNT(s_id)
:--- | ----------: | ---------: | ----------:
S01  |        2210 |         22 |           4
S02  |         100 |          9 |           1

db<>fiddle here

Upvotes: 1

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

Next query can solve the problem:

SELECT a.s_id
     , SUM(b.price) AS total_price
     , total_unit
     , count_s_id
  FROM A a
  JOIN B b ON b.a_id = a.id 
  JOIN (
    -- join pre-grouped table
    SELECT
        s_id,
        SUM(A.unit_value) AS total_unit,
        COUNT(s_id) count_s_id 
    FROM A GROUP BY s_id
  ) grouped_a ON grouped_a.s_id = a.s_id
 GROUP 
    BY a.s_id;

Test it on SQLize.online

Upvotes: 1

Related Questions