CODEWITHSUNDEEP
mysqldatabase
Herbert
Herbert

Reputation: 85

Aggregating multiple mysql tables

I have 3 MySQL tables that are below (I used excel to show example of my tables)

enter image description here

enter image description here

enter image description here

My intended result table is below enter image description here

I have tried sql below but i am not getting desired above result

select count(TableA.code),sum(TableB.Mass),count(TableC.Code) from 
TableB left join TableA on TableA.Code = TableB.Code left join TableC on 
TableA.Code = TableC.Code
group  by TableB.Grade

enter code here

Upvotes: 0

Views: 41

Answers (1)

Rahul Biswas
Rahul Biswas

Reputation: 3467

As per your given sample and desired result table C count and mass will be retrieved based on active status. That's why conditional CASE statement is used for table C value calculation.

 -- MySQL
SELECT b.grade
     , COUNT(a.code) "Table A (Code Count)"
     , SUM(b.Mass) "Table A (Total Mass)"
     , COUNT(CASE WHEN c.status = 'Active' THEN c.Code END) "Table C (Code Count)"
     , COALESCE(SUM(CASE WHEN c.status = 'Active' THEN b.Mass END), 0) "Table C (Total Mass)"
FROM TableB b 
LEFT JOIN TableA a
       ON a.code = b.code 
LEFT JOIN TableC c
       ON c.code = b.code
GROUP BY b.grade

Please check from url https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=43a9548e7bbdf049928982580e1a2cdd

Upvotes: 1

Related Questions