[thelist] further info (d'oh! was RE: SQL aggregation ignorance)

Brian Cummiskey Brian at hondaswap.com
Thu Jul 20 13:37:14 CDT 2006

Canfield, Joel wrote:
> so for DATEPART I should be looking at DAY(date), MONTH(date), etc.
Joel, I'd suggest doing this all in the query rather than parsing it out 
in the ASP code.  It will be much more efficient.

declare @date datetime
set @date = getdate()
select convert(varchar(12), @date, 101)

That little snipit will give you just the date.  (email me off list if 
you want a user defined function based off this for ms sql 2000 that 
will return the date only in a datetime field.)

> question still stands, though: does this make sense? any pointers?

Without seeing the table schema, i can't offer any suggestion that will 
help.  Is this all one table or multiple tables?

>> I can't fathom how to do this without multiple trips to the database,
>> which irks me.
It can be done, but again, it depends on how the tables are related, if 
at all.
>> http://www.spinhead.com/GetRowsSample.html
>> I can't fathom how to get the results aggregated (counts, sums, and
>> averages) by date.

average might throw some problem, but sum and count should be fairly 
simple to come up with a query.

More information about the thelist mailing list