Grouping and Average Question

Hi,
My problem is as follows:
I would like to take the average value of count column grouping by drive
letter and date.
Sample table
DriveLetter Date Count
K: 2005-07-05 06:00:00:000 33.555
K: 2005-07-05 06:30:00:000 35.555
K: 2005-07-05 07:00:00:000 48.555
K: 2005-07-05 07:30:00:000 52.555
h: 2005-07-05 06:00:00:000 33.555
h: 2005-07-05 06:30:00:000 35.555
h: 2005-07-05 07:00:00:000 48.555
h: 2005-07-05 07:30:00:000 52.555
i: 2005-07-05 06:00:00:000 33.555
i: 2005-07-05 06:30:00:000 35.555
i: 2005-07-05 07:00:00:000 48.555
i: 2005-07-05 07:30:00:000 52.555
Thanks
MikeDragon9994 wrote:

> I would like to take the average value of count column grouping by
> drive letter and date.
> Sample table
> DriveLetter Date Count
> K: 2005-07-05 06:00:00:000 33.555
> K: 2005-07-05 06:30:00:000 35.555
> K: 2005-07-05 07:00:00:000 48.555
> K: 2005-07-05 07:30:00:000 52.555
select DriveLetter, CAST(CONVERT(char(8), [date], 112) AS DATETIME) as
[Date], avg([Count]) as AvgCount
from SampleTable
group by DriveLetter, CAST(CONVERT(char(8), [date], 112) AS DATETIME)
HTH,
Stijn Verrept.|||>> .. average value of count column grouping by drive
letter and date. <<
DATE and COUNT are reserved words in SQL.
SELECT drive_letter, foobar_date, AVG(foobar_count)
FROM Foobar
GROUP BY drive_letter, foobar_date;
I have no Stijn wants to CAST() temporal data into strings. I also
have no idea why he is also using CONVERT() unless he likes proprietary
code.|||--CELKO-- wrote:

> DATE and COUNT are reserved words in SQL.
Very true that date and count reserved words are, it's indeed better
not to use them, that's also why I put them between brackets.

> I have no Stijn wants to CAST() temporal data into strings. I also
> have no idea why he is also using CONVERT() unless he likes
> proprietary code.
Well if you check the original message you'll see that his sample date
column also contains hours and he wanted to group by date.
It maybe is a little confusing since the OP also uses Date as the
column name so it's not sure if he wants to sort by date or by the
column date (which also contains the time). HOWEVER, if you look at
the sample data you'll see that every hour only occurs once per drive
letter so it would be useless to get an average value, that why we can
be pretty sure that the OP means date (in date without time).
That's why your query is pretty much useless. If you run that query on
the data he has supplied you'll get exactly that same data back.
The CAST(CONVERT(char(8), [date], 112) AS DATETIME) is used to get rid
of the time and only look at the date.
Hope this clears things up,
Stijn Verrept.|||Thanks for your help. It is what I needed to do.
Sorry for the confussion on the Date Column.
Mike
"Stijn Verrept" wrote:

> --CELKO-- wrote:
>
> Very true that date and count reserved words are, it's indeed better
> not to use them, that's also why I put them between brackets.
>
>
> Well if you check the original message you'll see that his sample date
> column also contains hours and he wanted to group by date.
> It maybe is a little confusing since the OP also uses Date as the
> column name so it's not sure if he wants to sort by date or by the
> column date (which also contains the time). HOWEVER, if you look at
> the sample data you'll see that every hour only occurs once per drive
> letter so it would be useless to get an average value, that why we can
> be pretty sure that the OP means date (in date without time).
> That's why your query is pretty much useless. If you run that query on
> the data he has supplied you'll get exactly that same data back.
> The CAST(CONVERT(char(8), [date], 112) AS DATETIME) is used to get rid
> of the time and only look at the date.
> --
> Hope this clears things up,
> Stijn Verrept.
>