August 21, 2003 at 5:47 am
i am using the foolwing code in a lookup
SELECT MAX(QUANTITY) AS MAX_QUANTITY, MIN(QUANTITY) AS MIN_QUANTITY, AVG(QUANTITY) AS AV_QUANTITY
FROM TRANSACTIONS
WHERE (DATE_TIME = ?)
this gives me avg,max and min only for a specific date what i actually want is that it should give me avg,max and min..for all the dates in the transactions table what should i do for this
August 21, 2003 at 6:00 am
If you want to return min, max and avg for each date then use a group by function
i.e. group by date_time.
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself.
August 21, 2003 at 10:12 am
Good suggestion from _brokenrulz_, but be aware that a group by on a date field will work well if your field is populated just to the date ('2003-08-21 00:00:00.00'), but will require you to do more work if the dates are fully populated, as would be the case if populated with getdate() (ie., 2003-08-21 10:35:22.123). If that is the case, you will need to reduce the dates to the date only portion somehow.
RW
August 21, 2003 at 10:28 pm
quote:
but be aware that a group by on a date field will work well if your field is populated just to the date ('2003-08-21 00:00:00.00'), but will require you to do more work if the dates are fully populated, as would be the case if populated with getdate() (ie., 2003-08-21 10:35:22.123). If that is the case, you will need to reduce the dates to the date only portion somehow.
Thanx for highlighting the fact, missed it out 😉
The dates can be reduced to the date portion only, by using the convert function.
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself.
August 22, 2003 at 3:28 pm
This will group by day no matter what time is in datetime.
SELECT
cast(convert(varchar, DATE_TIME, 101) as datetime) as DATE_TIME,
MAX(QUANTITY) AS MAX_QUANTITY,
MIN(QUANTITY) AS MIN_QUANTITY,
AVG(QUANTITY) AS AV_QUANTITY
FROM TRANSACTIONS
group by cast(convert(varchar, DATE_TIME, 101) as datetime)
Signature is NULL
August 23, 2003 at 2:12 am
SELECT
cast(convert(varchar, DATE_TIME, 101) as datetime) as DATE_TIME,
MAX(QUANTITY) AS MAX_QUANTITY,
MIN(QUANTITY) AS MIN_QUANTITY,
AVG(QUANTITY) AS AV_QUANTITY
FROM TRANSACTIONS
group by cast(convert(varchar, DATE_TIME, 101) as datetime)
the above given code does not work in my case i guess this is becoz i hve an oracle Db at the backend from where i am using the data..what i actullay want is that it should show avg,max,min quantity for every day..date wise..
one more thing can i use group by clause with a column and alos select some other columns on which i am not using any group function..how is this possible..
any solutions plz..
August 26, 2003 at 11:45 am
sairah, I'm not to familiar with Oracle...is it the "convert" syntax that's the problem?
Regarding group by: You must have a 1 to 1 relationship for all records in a grid. In other words, two cells from one column cannot be associated with one row. In order to prevent this you can only select columns that you group by. It makes sense if you think about it. If you want to select any other columns you have to do an aggregate function on them (IE: min(), max(), sum(), etc).
Signature is NULL
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply