Rick Roen 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?

and

> I need the CUSTOMER to have ALL Sum(O.Total) by SEASON to be over 1000
> or
> not ANY Sum(O.Total) by SEASON < 1000

Hmm, this sounds unusual and requires some thinking.

SELECT C.*
from CUSTOMERS C
where not exists(Select O.Season, SUM(O.Total) From ORDERS O
where O.CUSTNUM = C.IDNUM Group by O.Season HAVING SUM(O.Total) < 1000)
and not exists(select * from ORDERS O2
where not exists(select * from ORDERS O3 where O3.CustNUM = C.IDNUM
and O3.Season = O2.Season))

Now, I've never tried using GROUP BY within a subselect and don't know
whether that works in Firebird 1.5 (I doubt it). The second not exists
(the one that is nested) should work and ensures that customers with no
orders in a season are eliminated from the result.

Tell us whether this works, if not I think you have to

CREATE VIEW SEASONSALE (Season, CustNum, Total) as
SELECT O.Season, O.CustNum, Sum(O.Total) GROUP BY 1,2

commit and then

SELECT C.*
from CUSTOMERS C
where not exists(Select * from SeasonSale S
where S.CUSTNUM = C.IDNUM and S.Total < 1000)
and not exists(select * from ORDERS O2
where not exists(select * from ORDERS O3 where O3.CustNUM = C.IDNUM
and O3.Season = O2.Season))

HTH,
Set