--- In firebird-support@yahoogroups.com, "Adam" <s3057043@y...>
wrote:

>
> 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

>

For the records, no it doesn't work.

> 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
>

Thanks Adam, You're right and I will follow your advice about
descriptor field naming and also setting up the initial field types
(casting).

Thanks for the comprehensive reply, your time and help!