ClaireUK
|
| Posted: 03/19/2003, 2:58 AM |
|
Help!...I have no SQL knowledge at all, I'm creating a statistics application, and really need to know how to display the results of mathmatical operations on dynamic data,
One example: I need to find the lowest number in a column, and JUST display that number from the column.
I also need to be able to find the average of a series of numbers from one column.....I assume that I need to do this with an SQL string, but I have no idea how!
Please help an english girl in distress!!
|
|
|
 |
DaveRexel
|
| Posted: 03/19/2003, 4:09 AM |
|
Hi Claire
Most of your needs can be covered by altering the generated SQL.
example : SELECT MIN(my_nr) FROM my_table WHERE my_condition
I recommend http://sqlcourse.com/intro.html
as a very good online sql primer.
Hope this helps
Greetings
Dave
www.rexdesign.com
|
|
|
|
ClaireUK
|
| Posted: 03/19/2003, 1:38 PM |
|
Thankyou Dave,
thats been most helpful...
Claire.
|
|
|
|
ClaireUK
|
| Posted: 03/19/2003, 2:27 PM |
|
OK i got the general idea, I'm trying to show the fastest lap and its driver, firstly, I got code charge to just display all the records in the field I wanted and the SQL came out like this:
sSQL = "select [L].[Laptime] as L_Laptime " & _
" from [Laps] L
Cool, so I change the SQL to look like this:
sSQL = "select MIN([L].[Laptime]) as L_Laptime " & _
" from [Laps] L "
The page displays the fastest lap...excellent.
Confidence gets the better of me, I decide to add the User field (a numeric value, related to a user record in another table called, Users)
I generate the page and overwrite my changes...the SQL comes out like this:
sSQL = "select [L].[Laptime] as L_Laptime, " & _
"[L].[User] as L_User " & _
" from [Laps] L "
so I do the logical thing:
sSQL = "select MIN([L].[Laptime]) as L_Laptime, " & _
"[L].[User] as L_User " & _
" from [Laps] L "
Oh woe is me...an error page:
Microsoft JET Database Engine (0x80040E21)
You tried to execute a query that does not include the specified expression 'L_User' as part of an aggregate function.
/stats/Common.asp, line 34
AAAAHHHH! Confusion reigns!!
Claire
|
|
|
|
DaveRexel
|
| Posted: 03/19/2003, 3:18 PM |
|
Hi again,
Without knowing the structure of your DB tables it's hard to advise you.
Regards
Dave
|
|
|
|
Brandon
|
| Posted: 03/20/2003, 9:35 AM |
|
You could include the driverID field in a GROUP BY clause. But that will give you each drivers fastest time and that probably isn't what you want.
In MS SQL Server to can combine a ORDER BY time and SELECT TOP 1 to get the first row.
|
|
|
|
Mike Curry
|
| Posted: 03/20/2003, 10:00 AM |
|
Claire, is the db Access? Access has peculiarities regarding what it likes in an SQL query, especially where ODBC is concerned. You will have to read up a bit on those features.
|
|
|
|
|
