Average of a subsample

# Thread: Average of a subsample

1. Senior Member
Join Date
Dec 1969
Posts
406

## Average of a subsample

Hello,<BR><BR>Using some of Bill&#039;s advice, I&#039;m trying to calculate an average based on not all of the data, but only the 5 most recent events.<BR><BR>Bill suggested *Something* like this:<BR><BR>SELECT AVG(XXX) FROM info<BR>WHERE eventDate IN (<BR>SELECT TOP 5 eventDate<BR>FROM info<BR>ORDER BY eventDate DESC )<BR><BR>To which I&#039;ve come up with the following:<BR><BR>dim strQ3, objRS3<BR>strQ3="SELECT AVG([Order Details].Quantity) as recentavg"<BR><BR>strQ3 = strQ3 & "FROM Orders INNER JOIN [Order Details] ON "<BR>strQ3 = strQ3 & "Orders.OrderID = [Order Details].OrderID "<BR>strQ3 = strQ3 & "WHERE Orders.Orderdate IN (SELECT TOP 5 Orders.Orderdate "<BR>strQ3 = strQ3 & "FROM Orders INNER JOIN [Order Details] ON "<BR>strQ3 = strQ3 & "Orders.OrderID = [Order Details].OrderID "<BR>strQ3 = strQ3 & "Order by Orders.Orderdate DESC) "<BR>Response.Write "DEBUG: SQL is " & strQ3 & "&#060;HR&#062;"<BR>set objRS3 = objconn.Execute (strQ3)<BR><BR>This returns the error "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.", and does not display my debug. I think I am just getting confused by the nesting issues. Any thoughts?

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## What's the point...

...of the JOIN in that inner query?<BR><BR>Yes, you need it in the outer query, but...<BR><BR>You don&#039;t really care which ORDERID is involved, do you? You just want the most recent ORDERDATE values no matter what the order id.<BR><BR>But I *think* I see your problem. And I&#039;ll be if you look REAL close at the DEBUG output, you would, too.<BR><BR>There&#039;s no space before the word FROM in the first query.<BR><BR>This is *definitely* a query I would have tried in the query designer before dropping it into ASP code.<BR><BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
406

## RE: What's the point...

Hey Bill,<BR><BR>I think I got it working! Thanks. Am I missing something? -- I don&#039;t know how to check the debug since it failed before it got there. <BR><BR>I made the initial query in designer, but didn&#039;t know how to make a more complicated one. I will look into it for next tim. It will undoubetly make life easier.<BR><BR>In case anyone is ever interested in a similar problem, the final code was:<BR><BR>dim strQ2, objRS2<BR>strQ2="SELECT AVG([Order Details].Quantity) as quantityavg "<BR>strQ2 = strQ2 & "FROM Orders INNER JOIN [Order Details] ON "<BR>strQ2 = strQ2 & "Orders.OrderID = [Order Details].OrderID "<BR>set objRS2 = objconn.Execute (strQ2)<BR><BR>dim strQ3, objRS3<BR>strQ3="SELECT AVG([Order Details].Quantity) as recentavg "<BR>strQ3 = strQ3 & "FROM Orders INNER JOIN [Order Details] ON "<BR>strQ3 = strQ3 & "Orders.OrderID = [Order Details].OrderID "<BR>strQ3 = strQ3 & "WHERE Orders.Orderdate IN (SELECT TOP 5 Orders.Orderdate "<BR>strQ3 = strQ3 & "FROM Orders "<BR>strQ3 = strQ3 & "Order by Orders.Orderdate DESC) "<BR>Response.Write "DEBUG: SQL is " & strQ3 & "&#060;HR&#062;"<BR>set objRS3 = objconn.Execute (strQ3)<BR>

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Just type it in as SQL...

...you don&#039;t *have* to use the graphical designer. Just choose the "SQL View" and type in whatever you feel like.<BR><BR>Or do part of the design graphically and then finish up by editing the SQL it produces.<BR><BR>Anyway, good enough!<BR><BR>

#### Posting Permissions

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