SQL Statement ?
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.
Desktop
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.
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...
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 aplatfl
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
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.
by aplatfl
This discussion is closed to new replies.
Sort by Trending