Select C.IDNUM, O.SEASON, Sum(O.TOTAL)
from CUSTOMERS C
inner join ORDERS O on O.CUSTNUM = C.IDNUM
group by C.IDNUM. O.SEASON
having Sum(O.TOTAL) > 1000

On 24/01/07, Rick Roen <rick@...> wrote:
> FB 1.5.3
>
> I need some SQL help.
>
> I need to qualify customers that have sales of over 1000 in a "SEASON"
> which is a field in the ORDERS table.
>
> My simplified SQL:
>
> Select C.*
> from CUSTOMERS C
> where 1000 > ALL(Select Sum(O.TOTAL) from ORDERS O where O.CUSTNUM =
> C.IDNUM Group by O.SEASON)
>
> but this is returning Customers that do not even have one sales, much
> less all SEASON's sales over 1000.
>
> Can someone shed some light on how to do this?
>
> Rick
>