Oledb select and count statement I have a lottery database with columns Ball1 to Ball6 how do i find number of times a ball has been drawn from columns Ball1 to Ball6
For modern MySQL version:

WITH cte AS (          SELECT Ball1 ball FROM lottery
             UNION ALL SELECT Ball2      FROM lottery  
             UNION ALL SELECT Ball3      FROM lottery
             UNION ALL SELECT Ball4      FROM lottery
             UNION ALL SELECT Ball5      FROM lottery
             UNION ALL SELECT Ball6      FROM lottery)
SELECT ball, COUNT(*) cnt
FROM cte
GROUP BY ball


For ancient MySQL version:

SELECT ball, COUNT(*) cnt
FROM (          SELECT Ball1 ball FROM lottery
      UNION ALL SELECT Ball2      FROM lottery
      UNION ALL SELECT Ball3      FROM lottery
      UNION ALL SELECT Ball4      FROM lottery
      UNION ALL SELECT Ball5      FROM lottery
      UNION ALL SELECT Ball6      FROM lottery) cte
GROUP BY ball