SQL joining a few count(*) group by selections

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.

From stackoverflow

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

  Yada : I understand the UNION part. The not sure how to do the aggregate part.

• 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
  

• 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
  

  Tomalak : Yeah, seen it too late. All edits before the fifth minute are hidden, it inserted the "ALL" at 05:30 mins. :-D

  karlis : thats all magic. :)

• 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