CODEWITHSUNDEEP
mysqlsql
Charles Mendoza
Charles Mendoza

Reputation: 5

How to combine 3 tables having same column names in MySQL?

How do I achieve the objective output?

TABLE: degree

region       degree 
-------------------
Cavite          2
Manila          2

TABLE: nondegree

region      non-degree
----------------------
Cavite          2
Manila          2
Laguna          2

TABLE: shortcourse

region      short-course
------------------------
Cavite          2
Laguna          2


OBJECTIVE:

region    degree    non-degree  short-course   total
----------------------------------------------------
Cavite      2           2           2           6
Manila      2           2           0           4
Laguna      0           2           2           4

I have tried using union and union all but all I'm getting is this kind of output

region       degree 
-------------------
Cavite          2
Manila          2
Cavite          2
Manila          2
Laguna          2
Cavite          2
Laguna          2

I'm getting ambiguous errors from joining these 3 tables because of the region column.

Upvotes: 0

Views: 147

Answers (1)

Akina
Akina

Reputation: 42834

SELECT region, 
       SUM(degree) degree, 
       SUM(nondegree) nondegree, 
       SUM(shortcourse) shortcourse, 
       SUM(degree) + SUM(nondegree) + SUM(shortcourse) total
FROM ( SELECT region, degree, 0 nondegree, 0 shortcourse
       FROM degree
     UNION  ALL
       SELECT region, 0, nondegree, 0
       FROM nondegree
     UNION  ALL
       SELECT region, 0, 0, shortcourse
       FROM shortcourse ) total_data
GROUP BY region

Upvotes: 1

Related Questions