displaying a frequency of table values

g-forcewebmedia · #1 (**permalink**) 06-02-08, 17:06

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

Quote:

"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
						%>

myle · #2 (**permalink**) 06-19-08, 02:25

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

gvee · #3 (**permalink**) 06-19-08, 03:42

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

Internet Services

Web Development

Digital Marketing

Consumer Tech