> Can't
> understand why - assume the internal data structures must be different
> between, Max, Min, Avg, Count and Sum.
> I suppose the moral is "When in doubt Cast"?
>
> Regards,
>

Tony,

Consider the following simplified example, assuming Col_1 and Col_2
are some sort of integer.

MyTable:

Col_1 Col_2
----- ------
1 1
2 2

Now your query looks like this:

Select 'Minimum', Min(Col_1), Min(Col_2) from MyTable
Union
Select 'Maximum', Max(Col_1), Max(Col_2) from MyTable
Union
Select 'Average', Avg(Col_1), Avg(Col_2) from MyTable

Firebird uses the first query within the union to determine the
required datatypes, so it decides that the first column is to be a
char or varchar, the second column is to be integer and the third
column is to be integer, so the first record to be returned is:

'Minimum', 1, 1

All is well so far, so the first union is done, and the result looks
like this so far:

'Minimum', 1, 1
'Maximum', 2, 2

Now the third union is attempted but there is a problem. Avg Col_1 is
1.5, and it isn't going to fit inside an integer field. I mean
Firebird could force it to fit by rounding it, but then your results
would not be as expected. So you end up with an error message.

Select 'Average', Avg(Col_1), Avg(Col_2) from MyTable
Union
Select 'Minimum', Min(Col_1), Min(Col_2) from MyTable
Union
Select 'Maximum', Max(Col_1), Max(Col_2) from MyTable

Does this work? I haven't tested it at all, but I think this one will
force the field to become a float which can hold both the result of
avg and an integer without problem.

BUT

It is one thing to explain the phenomenon, it is another to come up
with a solution that works every time.

Firstly, you need to name the fields or the field names are going to
be things like Min_1 etc, with no guarantee of not being renamed in a
future version of Firebird, and with no sensible meaning for Maximum
or Average.

Secondly, you are lucky in this case that the description field is the
same length on all lines so that will not cause the issue. Although
the following query is a bit longer, it should work better.

Select Cast('Minimum' as varchar(7)) as Description, Cast(Min(Col_1)
as float) as Col_1, Cast(Min(Col_2) as float) as Col_2 from MyTable
Union
Select 'Maximum', Max(Col_1), Max(Col_2) from MyTable
Union
Select 'Average', Avg(Col_1), Avg(Col_2) from MyTable

Also, given that duplicates are not possible in this query, union is
equivalent to union all. Union all does not have to check for
duplicates, which may or may not make a difference to performance.

(It is like running select distinct pk_field from mytable, there is no
advantage in using distinct here).

Hope that helps
Adam