Search code examples
sqlunion

Treat Multiple Columns as 1 in SQL to Get Aggregates

Is it possible to get counts, too? My UNIONs get me all distinct values across all 4 columns but now I need to know how many times each value appears across all 4 columns. Need to stay with stock SQL, if possible.

enter image description here

(SELECT DISTINCT classify1 AS classified FROM class) UNION
(SELECT DISTINCT classify2 AS classified FROM class) UNION
(SELECT DISTINCT classify3 AS classified FROM class) UNION
(SELECT DISTINCT classify4 AS classified FROM class)
ORDER BY classified

Returns:

A
B
C
D
E
F
H

Need:

A | 3
B | 3
C | 4
D | 3
E | 1
F | 1
H | 1

Solution

  • SQL Fiddle

    SELECT a.classified, COUNT(*)
FROM
(
  (SELECT classify1 AS classified FROM class) UNION ALL
  (SELECT classify2 AS classified FROM class) UNION ALL
  (SELECT classify3 AS classified FROM class) UNION ALL
  (SELECT classify4 AS classified FROM class)) a
GROUP BY a.classified

    Result

    | CLASSIFIED | COLUMN_1 |
-------------------------
|          A |        3 |
|          B |        3 |
|          C |        4 |
|          D |        3 |
|          E |        1 |
|          F |        1 |
|          H |        1 |

    When you use DISTINCT you eliminate the extra 'A' in classify3