Post reply

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

  • May 19, 2015 at 6:03 am

    #308317

    hi

    I have the following data and I need to be able to make a chart to get frequency as to which number occurs within "Ball 1" to "Ball 6" column

    Draw DateBall 1Ball 2Ball 3Ball 4Ball 5Ball 6

    12/16/20001418192844

    11/11/2000135262933

    10/21/20001719323437

    8/19/200011118222528

    6/24/2000178262733

    6/10/20001624254547

    4/1/200015 1122 4042

    3/18/20001732354647

  • 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!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply