Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2008
    Posts
    3

    Unanswered: displaying a frequency of table values

    Hello i am trying to get my asp to be displayed as:


    "Num1" (the input number variable from previous form) "-" & "(lottery number with highest frequency)"

    "Num1" (the input number variable from previous form) "-" & "(lottery number with 2nd highest frequency)"

    ...
    with the code that I have below it currently takes every number in the database and tallies it. However I need it to be displayed so that only certain rows that contain a user inputted number show up.the database is basically 6 columns, each with a number. I tried to implement a WHERE statement but of course it can't work because it is connected to an aliased select. the WHERE statement I had is:

    Code:
    WHERE (number1='" & Num1 & "') OR (number2='" & Num1 & "') OR (number3='" & Num1 & "') OR (number4='" & Num1 & "') OR (number5='" & Num1 & "') OR (number6='" & Num1 & "')
    The code that is running internally in the database as a union query:

    Code:
    SELECT number1 AS LotNum
    FROM LotteryNumberList
    UNION ALL SELECT number2 as LotNum
    FROM LotteryNumberList
    UNION ALL SELECT number3 as LotNum
    FROM LotteryNumberList
    UNION ALL SELECT number4 as LotNum
    FROM LotteryNumberList
    UNION ALL SELECT number5 as LotNum
    FROM LotteryNumberList
    UNION ALL SELECT number6 as LotNum
    FROM LotteryNumberList;
    this is the code that I am using on the asp page:

    Code:
    <%
    
    Set dbaseConn = Server.CreateObject("ADODB.Connection")
    dbaseConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("\WednesdayNumbers2008Trial.mdb") & ";"
    
    ' Now, create the SQL statement
    QryFrequency = "SELECT QryUnionLottery.LotNum, Count(QryUnionLottery.LotNum) AS Frequency
    FROM QryUnionLottery
    GROUP BY QryUnionLottery.LotNum
    ORDER BY Count(QryUnionLottery.LotNum) DESC;"
    
    
    ' Execute the SQL statement, and set the recordset object
    ' to the result of this execution. We obtain the resulting
    ' records in Rs object
    Set RS = dbaseConn.Execute(QryFrequency)
    
    
    
    ' Use this RecordSet object to populate your HTML output stream
    ' In this example, we will just write out the last name field
    Do While NOT Rs.EOF
      Response.Write(Num1)
      Response.Write("-")
    Response.Write(Rs.Fields("LotNum").value)
    Response.Write("<br/>")
      ' Move to the next record in the resultset
      Rs.MoveNext
    Loop
    
    ' Close the Recordset object and destroy it
    Rs.Close
    Set Rs = Nothing
    
    ' You might want to release the resources for connection object,
    ' unless you want to use the same connection again in the later code
    dbaseConn.Close
    Set dbaseConn = Nothing
    						%>

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    I me won't use UNION table as that will over work the CPU H E A P S
    what I do is run the Union locally and turn it into a table

    need to think on how offen the data been updated

    don't think this will help your problem but
    could get you think down a differance track
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Wow, your column names scream bad design!
    You could re-write your where clause like this by the by
    Code:
    WHERE   num1 IN (number1, number2, ... , numberN)
    As you have found, with the design you have implemented, even the simplest queries become a heck of a lot more complex and inefficient.

    If you had chosen a table structure such as
    LotteryResults(draw_date, number)
    Then your query would be as simple as pie
    Code:
    DECLARE @LotteryResultstable (draw_date datetime, number int)
    
    INSERT INTO @LotteryResults (draw_date, number)
          SELECT '20080101', 1
    UNION SELECT '20080101', 13
    UNION SELECT '20080101', 27
    UNION SELECT '20080101', 31
    UNION SELECT '20080101', 38
    UNION SELECT '20080101', 41
    UNION SELECT '20080107', 7
    UNION SELECT '20080107', 9
    UNION SELECT '20080107', 13
    UNION SELECT '20080107', 17
    UNION SELECT '20080107', 33
    UNION SELECT '20080107', 39
    UNION SELECT '20080114', 2
    UNION SELECT '20080114', 9
    UNION SELECT '20080114', 13
    UNION SELECT '20080114', 19
    UNION SELECT '20080114', 20
    UNION SELECT '20080114', 46
    
    SELECT TOP 2
           number
         , Count(*) As [frequency]
    FROM   @LotteryResults
    GROUP
        BY number
    ORDER
        BY Count(*) DESC
    Code:
    number      frequency   
    ----------- ----------- 
    13          3
    9           2
    
    (2 row(s) affected)
    I urge that you go back and re-think your design
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •