Upsizing Problems with Aggregate Functions

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

I'm upsizing the data.mdb portion of my app to SQL Server, and running into
some problems with certain queries. Here is an example:

SQL:
SELECT Groups.Group, Format(Avg([D_Farrow]),"Short Date") AS AvgDate
FROM Groups INNER JOIN Farrowing ON Groups.Group = Farrowing.Group
GROUP BY Groups.Group;

Error Message (or something similar).:
The sum or aggregate function cannot take a datetime as an average.

Any ideas on the workaround?

Thanks.

e
 
According to SQL Server's BOL (Books Online), Avg only works with Integer,
Decimal, Money (and Smallmoney) and Float (and Real) data types.

To be honest, I don't understand what an "average date" would be anyhow.
 
We use average date extensivly throughout the application;

There are groups of animals that have litters, the average date is the date
we use for a collective birth date.

Any ideas for a work around?

Thanks

e


Douglas J. Steele said:
According to SQL Server's BOL (Books Online), Avg only works with Integer,
Decimal, Money (and Smallmoney) and Float (and Real) data types.

To be honest, I don't understand what an "average date" would be anyhow.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Eric said:
I'm upsizing the data.mdb portion of my app to SQL Server, and running into
some problems with certain queries. Here is an example:

SQL:
SELECT Groups.Group, Format(Avg([D_Farrow]),"Short Date") AS AvgDate
FROM Groups INNER JOIN Farrowing ON Groups.Group = Farrowing.Group
GROUP BY Groups.Group;

Error Message (or something similar).:
The sum or aggregate function cannot take a datetime as an average.

Any ideas on the workaround?

Thanks.

e
Click to expand...
Click to expand...
 
You can modify your Access app to use Double fields instead
of Date, and upsize again.

You would have to modify you application to use explicit
date conversions and formatting.

Or you could try to modify your queries to use explicit
casts (to double or long) - I've never tried that with
aggregates and SS: you might have to migrate your aggregate
queries into the server database.

(david)


Eric said:
We use average date extensivly throughout the application;

There are groups of animals that have litters, the average date is the date
we use for a collective birth date.

Any ideas for a work around?

Thanks

e


Douglas J. Steele said:
According to SQL Server's BOL (Books Online), Avg only works with Integer,
Decimal, Money (and Smallmoney) and Float (and Real) data types.

To be honest, I don't understand what an "average date" would be anyhow.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Eric said:
I'm upsizing the data.mdb portion of my app to SQL Server, and running into
some problems with certain queries. Here is an example:

SQL:
SELECT Groups.Group, Format(Avg([D_Farrow]),"Short Date") AS AvgDate
FROM Groups INNER JOIN Farrowing ON Groups.Group = Farrowing.Group
GROUP BY Groups.Group;

Error Message (or something similar).:
The sum or aggregate function cannot take a datetime as an average.

Any ideas on the workaround?

Thanks.

e
Click to expand...
Click to expand...
Click to expand...
 
Back
Top