Hello Tony,

> > Ehm, are you doing the CAST with the 3rd union as well?
> >
> > With regards,
> >
> Hi Martijn,
>
> The problem was with the AVG() - the return data type was not
> compatible with the Min and Max -
> i.e Select 'Average', Cast(Min(Col_1) as Smallint), Cast(Min(Col_2) as
> Smallint) from MyTable etc works OK.
> Think I may have misunderstood what you were trying to say and only
> cast the first string - i.e Cast('Average' as Varchar(9)) instead of
> the whole statement.
> Changing the first select to Avg() (from Min()) resulted in the same
> error even with one union (it just happened that AVG() was the last of
> the two UNIONS).
> Casting the AVG() to smallint (as per the column datatype) solved the
> problem but I don't understand why when I tried Count() and SUM() also
> gave the same error as AVG() - one would have assumed that they would
> give the same return type as Min() and Max()?

Well, COUNT and SUM can overflow the SMALLINT easily :-)
If you're going to throw these in the mix, better "up cast" them to some
wider datatype.

AVG, I figured, should return the same datatype, but apparently not.

Well, you got the hang of it now :-)

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com