>Hi All
>I have the following two tables:
>Reservations ( ResNo C(6),ClientCode C(5),Amount N(9,2), Date D )
>Clients ( Code C(5), ClientName C(30) )
>I want to retrieve in one SELECT the number of reservations and the total amount grouped by client, for two time periods ( current and last year ). The current time period will be BETWEEN ( date, ReportFrom, ReportTo ), and the old time period will be BETWEEN( date, OldFrom, OldTo ).
>I am trying to run the following select command:

>SELECT Clients.ClientName, Count(Reservations.ResNo) as CurRes,;
> sum(Reservations.Amount) as CurAmount,0000 as OldRes,00000.00 as Oldamount ;
> from Reservations, Clients ;
> where Reservations.ClientCode=Clients.Code and ;
>   between( Reservations.date, ReportFrom, ReportTo ) ;
> group by 1 ;
>union ( ;
>SELECT Clients.ClientName, 0000 as CurRes,00000.00 as CurAmount,;
> count(Reservations.ResNo) as OldRes,sum(reservations.amount) as Oldamount ;
> from Reservations, Clients ;
> where Reservations.ClientCode=Clients.Code and ;
>   between( Reservations.date, OldFrom, OldTo ) ;
> group by 1 )

>I have two problems: first, when running this, some clients come once, and some twice. Also, i tried to put an order by clause, but wherever i put it ( i.e in the end of the fist select ) i get a "Missig ) parentheses" error.
>
>TIA
>Jaime

Hi,

Depending on what you need, you can use group by for subsets of union, or for entire union and get different results. If you want to group subsets above separately, then /group by 1/ confuse the entire union.
Depending on what you need try:

select....
group by Clients.ClientName..
UNION
select....
group by Clients.ClientName..
order by 1

OR

select....
UNION
select....
group by 1
order by 1