[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