[thelist] SQL Problem
aardvark
roselli at earthlink.net
Tue May 8 18:48:02 CDT 2001
hey, i was thinking about your problem today... actually, i wasn't,
but i did something similar and remembered you had a question
related to SQL and grouping and the like ... i couldn't offer more at
the time (was running out), so sorry if it's a day late (2 days)...
anyway, the SQL i used today, using MS SQL 2000:
Select
OrderYear = datepart(year,FieldName),
OrderMonth = datepart(month,FieldName),
OrderDay = datepart(day,FieldName),
sCount = count(OtherFieldName)
from TableName
group by datepart(year,FieldName),
datepart(month,FieldName),
datepart(day,FieldName)
order by
datepart(year,FieldName) desc,
datepart(month,FieldName)desc,
datepart(day,FieldName) desc
very similar to rudy's, and his is probably superior, but you said
you had syntax problems, so thought it's send this over...
the gist of the query is that it breaks the date/time from a record
into its parts and groups then orders them by it, after counting how
many records have that *date*... very handy for listing how many
orders have come in in one day, for instance... it doesn't do the
average, but that's just a step away, and i can't do *all* the SQL,
where would be the fun in that?
> From: "aardvark" <roselli at earthlink.net>
>
> > From: "Marios Andreas Tofarides" <matofarides at hotmail.com>
> >
> > I have a problem with an SQL query. I am using ASP with SQL Server 7
> > on Windows NT 4.0 Server. The problem is this: I have this table
> >
> > Timestamp Value
> > ------------------------------------
> > 01/01/2000 01:00 10.8
> > 01/01/2000 02:00 8.6
> > 01/02/2000 01:00 9.7
> > 01/02/2000 02:00 9.5
> >
> > And I want to get the average value for each day. i.e. I want these
> > results
> >
> > Date Average
> > 01/01/2000 9.7
> > 01/02/2000 9.6
More information about the thelist
mailing list