O P O P - 2 months ago 6
SQL Question

Group by multiple rows

Tables:

a: org1, class, class_id
b: org2, class, class_id


I did a
left outer join b on a.class_id = b.class_id


Results:

org1 | org 2 | class
--------+---------+------------------
ENGLISH | ENGLISH | English 101
ENGLISH | ENGLISH | English 220
ENGLISH | COMM | Communication 360
ENGLISH | HISTORY | History 362
HISTORY | ENGLISH | English 366


I want to know if it's possible to achieve the following grouping via SQL or programmatically using the results data set from above?

ORG: ENGLISH
-----------------
English 101
English 220
Communication 360
History 362
English 366
-----------------
ORG: HISTORY
-----------------
English 366
History 362
-----------------
COMM
-----------------
Communication 360



  • Any class with an ENGLISH org1 or org2 should show under ENGLISH org.

  • Any class with a HISTORY org1 or org2 should show under HISTORY org.

  • Any class with a COMM org1 or org2 should show under COMM org.

  • If they have another org (Communication 360 is both ENGLISH/COMM), it should show under both ENGLISH org and COMM org.


Answer

Use a UNION:

SELECT t.org, t.class
FROM
(
    SELECT org1 AS org, class
    FROM a
    UNION ALL
    SELECT org2 AS org, class
    FROM b
) t
ORDER BY t.org, t.class
Comments