RE: The frequency with which certain numbers occurs (ie number is shown somewhere in the "Ball1" to "Ball 6" column

Lowell

SSC Guru

Points: 323506

May 19, 2015 at 7:19 am

#1799010

;WITH MyCTE([DrawDate],[Ball1],[Ball2],[Ball3],[Ball4],[Ball5],[Ball6])

AS

(

SELECT '12/16/2000',1,4,18,19,28,44 UNION ALL

SELECT '11/11/2000',1,3,5,26,29,33 UNION ALL

SELECT '10/21/2000',1,7,19,32,34,37 UNION ALL

SELECT '8/19/2000',1,11,18,22,25,28 UNION ALL

SELECT '6/24/2000',1,7,8,26,27,33 UNION ALL

SELECT '6/10/2000',1,6,24,25,45,47 UNION ALL

SELECT '4/1/2000',1,5,11,22,40,42 UNION ALL

SELECT '3/18/2000',1,7,32,35,46,47

)

SELECT BallNumber,Count(*) As Cnt FROM

(

SELECT [Ball1] AS BallNumber FROM MyCTE UNION ALL

SELECT [Ball2] AS BallNumber FROM MyCTE UNION ALL

SELECT [Ball3] AS BallNumber FROM MyCTE UNION ALL

SELECT [Ball4] AS BallNumber FROM MyCTE UNION ALL

SELECT [Ball5] AS BallNumber FROM MyCTE UNION ALL

SELECT [Ball6] AS BallNumber FROM MyCTE

) x GROUP BY BallNumber ORDER BY CNT DESC

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!