Wow, I was not aware of the UNION ALL clause.

Thanks Arno

Salim

--- In firebird-support@yahoogroups.com, "Arno Brinkman"
<firebird@a...> wrote:

> Hi,
>
> > Then execute the following query:
> >
> > SELECT SUM(AMOUNT)
> > FROM TBL
> > WHERE TYPE_OF_ROW = 0
> > GROUP BY TYPE_OF_ROW
> > UNION
> > SELECT AMOUNT
> > FROM TBL
> > WHERE TYPE_OF_ROW <> 0
> >
> > The expected result is:
> >
> > 11.00
> > 11.00
> > 12.00
> >
> > Instead, the result is
> >
> > 11.00
> > 12.00
>
> That's the different between UNION and UNION ALL. Without ALL

automaticly a

> DISTINCT is performed on the resultset.
>
> Use this query:
>
> SELECT
> SUM(AMOUNT)
> FROM
> TBL
> WHERE
> TYPE_OF_ROW = 0
> GROUP BY
> TYPE_OF_ROW
> UNION ALL
> SELECT
> AMOUNT
> FROM
> TBL
> WHERE
> TYPE_OF_ROW <> 0
>
> Regards,
> Arno Brinkman
> ABVisie