--- In firebird-support@yahoogroups.com, "Rick Roen" <rick@...>
wrote:

>
> My mistake in typing but I do want all Sums greater than 1000
>
> 1000 < All( sums by SEASON)
>
> To show an example
>
> Customer 1
> season 1 2000
> season 2 1500
> season 3 3000
>
> Customer 2
> season 1 1500
> season 2 2000
> season 3 500
>
> Customer 1 would qualify because all SEASONS > 1000
> Customer 2 would NOT qualify because Season 3 < 1000
>
> The problem is in aggregating the sales that make up the SEASON

sales

> totals and how to compare them all at once and either qualify or
> disqualify the customer.
>
>

Oh. Well, give this a whirl. The "not exists" makes sure that
there wasn't any season for which the total orders where under 1000.

The "exists" makes sure that there was in fact at least one order in
every season.

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 exists(select count(O.SEASON) from ORDERS O
where O.CUSTNUM = C.IDNUM
group by O.SEASON
having count(O.SEASON) = <# of seasons>)


Don't have any idea how slow this might be with a lot of data, and
I'm sure there's a better way, but inverting the first step was what
I wanted to point out (All > 1000 is the same as not(any < 1000)).

Michael D. Spence
Mockingbird Data Systems, In.c