O P O P - 1 year ago 59
SQL Question

Group by multiple rows


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

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


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?

English 101
English 220
Communication 360
History 362
English 366
English 366
History 362
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 Source

Use a UNION:

SELECT t.org, t.class
    SELECT org1 AS org, class
    FROM a
    SELECT org2 AS org, class
    FROM b
) t
ORDER BY t.org, t.class
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download