SQL joining a few count(*) group by selections

Question

I have the following table containing the winning numbers of 6/49 lottery.

+-----+------------+----+----+----+----+----+----+-------+
|  id | draw       | n1 | n2 | n3 | n4 | n5 | n6 | bonus |
+-----+------------+----+----+----+----+----+----+-------+
|   1 | 1982-06-12 |  3 | 11 | 12 | 14 | 41 | 43 |    13 |
|   2 | 1982-06-19 |  8 | 33 | 36 | 37 | 39 | 41 |     9 |
|   3 | 1982-06-26 |  1 |  6 | 23 | 24 | 27 | 39 |    34 |
|   4 | 1982-07-03 |  3 |  9 | 10 | 13 | 20 | 43 |    34 |
|   5 | 1982-07-10 |  5 | 14 | 21 | 31 | 34 | 47 |    45 |
|   6 | 1982-07-17 |  8 | 20 | 21 | 25 | 31 | 41 |    33 |
|   7 | 1982-07-24 | 18 | 25 | 28 | 33 | 36 | 42 |     7 |
|   8 | 1982-07-31 |  7 | 16 | 17 | 31 | 40 | 48 |    26 |
|   9 | 1982-08-07 |  5 | 10 | 23 | 27 | 37 | 38 |    33 |
|  10 | 1982-08-14 |  4 | 15 | 30 | 37 | 46 | 48 |     3 |
+-----+------------+----+----+----+----+----+----+-------+

I would like to create a frequency chart to see the occurrence of numbers in n1, n2,...,n6 columns.

SELECT n1, COUNT(n1) as freq1 FROM lottery GROUP BY n1;
SELECT n2, COUNT(n2) as freq2 FROM lottery GROUP BY n2;
SELECT n3, COUNT(n3) as freq3 FROM lottery GROUP BY n3;
SELECT n4, COUNT(n4) as freq4 FROM lottery GROUP BY n4;
SELECT n5, COUNT(n5) as freq5 FROM lottery GROUP BY n5;
SELECT n6, COUNT(n6) as freq6 FROM lottery GROUP BY n6;

Basically I'm trying to combine the above queries into one query.

Thanks in advance.

Answer 1

make a union: select n1 from lottery union select n2 from lottery .... and then make your aggregate!

Answer 2

Given your table layout, you can't combine the queries into a single one by any other means than to union your queries, or to union your base table into a transposed version:

SELECT n, COUNT(*) as freq FROM 
(
  SELECT n1 AS n FROM lottery
  UNION ALL
  SELECT n2 FROM lottery
  UNION ALL
  SELECT n3 FROM lottery
  UNION ALL
  SELECT n4 FROM lottery
  UNION ALL
  SELECT n5 FROM lottery
  UNION ALL
  SELECT n6 FROM lottery
) as transposed
GROUP BY n
ORDER BY COUNT(*) DESC

Answer 3

tomalak, i think you version will not return the right resultset. edit: you changed it, now its correct. :)

SELECT COUNT(*), n1 FROM
(
SELECT n1 FROM tbl1
UNION ALL SELECT n2 FROM tbl1
UNION ALL SELECT n3 FROM tbl1
) AS nicetbl
GROUP BY n1

Answer 4

I would do something very similar, but try to reduce the number of records returned by the sub query...

SELECT
    n                     AS [n],
    SUM([data].count_n)   AS [count_n]
FROM
    (
        SELECT n1 AS [n], COUNT(*) AS [count_n] FROM lottery GROUP BY n1
        UNION ALL
        SELECT n2 AS [n], COUNT(*) AS [count_n] FROM lottery GROUP BY n2
        UNION ALL
        SELECT n3 AS [n], COUNT(*) AS [count_n] FROM lottery GROUP BY n3
        UNION ALL
        SELECT n4 AS [n], COUNT(*) AS [count_n] FROM lottery GROUP BY n4
        UNION ALL
        SELECT n5 AS [n], COUNT(*) AS [count_n] FROM lottery GROUP BY n5
        UNION ALL
        SELECT n6 AS [n], COUNT(*) AS [count_n] FROM lottery GROUP BY n6
    )
    as [data]
GROUP BY
    [data].n
ORDER BY
    SUM([data].count_n) DESC