Discussion Locked

SQL Statement ?

0 helpful
7 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a table with 3 fields(StoreId, Date, SalesTotal). How can I write a sql statement to find the avg salestotal within a date range for a certain storeid?
Thanks in advance.

7 replies

by albert franco ii

I'll use theTable as the variable or constant which holds the table name/path, and all names that start with var are either variables or constants representing the value indicated. This is very simple SQL and should work on just about any system.

Select AVG(SalesTotal) AS StoreAvg
from theTable
where (StoreID = varStoreID)
and (Date <= varEndDate)
and (Date >= varStartDate)

Or this...

Select AVG(SalesTotal) AS StoreAvg
from theTable
where (StoreID = varStoreID)
having (Date <= varEndDate)
and (Date >= varStartDate)

I like to use AS since it helps create very readable code.

Depending on the actual database you use there may be ways to optimize these queries. Since you haven't given much detail I give the most generic possibilities...

by albert franco ii

Define your variables as variants. Empty fields in SQL contain NULL and null is incompatible with non-variant variables.

Example: Dim varStoreID As Variant

This is the only way to be safe when records can hold empty fields. You can later move the value into a more strongly typed variable after testing for the NULL value:

if (varStoreID <> NULL) then intVarStoreID = varStoreID

Good Luck,
Al

by vramji

nStoreId = 100
dStartDate = '23-JUN-1999'
dEndDate = '22-JUN-2000'

SELECT AVG(SALESTOTAL) FROM STORESTABLE FOR STOREID = nStoreId and date >= dStartDate And
Date <= dEndDate

by aplatfl

I get an error 'Invalid Use of Null' '94
I have data for the parameters i am looking for in the db. Also, I am using vb to connect to access 2000.
Thanks, for any help.

This discussion is closed to new replies.