Re: Aggregation Procedure?

Howard J. Rogers wrote:
> Calling all PL/SQL gurus (since I'm not one).
>
> Here's my data:
>
> AREA SITE FTR Plot SQUARE_MTR AGGFLAG
> 1 BRITPK BR1 1 10.00 Y
> 1 BRITPK BR1 2 10.00 Y
> 1 BRITPK BR1 3 15.00 N
> 1 BRITPK BS1 1 5.00 Y
> 1 BRITPK BS1 2 7.00 Y
> 1 ANDSO GR1 1 100.00 Y
> 1 ANDSO GR1 2 120.00 Y
> 1 ANDSO BR1 1 150.00 Y
>

[...]
>
> AREA SITE FTR Plot SQUARE_MTR AGGFLAG
> 1 BRITPK BR1 3 15.00 N
> 1 BRITPK BR1 9999 20.00 N
> 1 BRITPK BS1 9999 12.00 N
> 1 ANDSO GR1 9999 220.00 N
> 1 ANDSO BR1 1 150.00 N
>
> Note Plot 3 on BRITPK escaped aggregation because its aggflag was "N".
> The
> BR1 on ANDSO also escaped, because it was the only plot of that
> feature on
> the site.
>
> I wrote something in Visual Basic (!!) years ago to achieve something
> similar, and it involved searching through the table in AREA+SITE+FTR
> order,
> noting when there was a change, and bunging in the totals where
> appropriate.
>
> But I'd like to do it in PL/SQL... so if anyone has any ideas, I'd be
> grateful.
>

Hello Howard, why don't you use plain SQL? Here is my suggestion:

select

   area,site,ftr,9999,sum(square_mtr) square_mtr,'N' aggflag from

   agg_test
where

   aggflag='Y'
group by

   area,site,ftr
having count(*)>1
union
select

   area,site,ftr,plot,square_mtr,aggflag from

   agg_test
where

   aggflag='N'
union
select

   area,site,ftr,max(plot) plot,sum(square_mtr) square_mtr,'N' aggflag from

   agg_test
where

   aggflag='Y'
group by

   area,site,ftr
having count(*)=1;

First aggregate all rows with aggflag='Y' and count to aggregate >1, then add those rows with aggflag='N' and finally add those rows where there is only one row to aggregate.

Or did I totally misunderstand your problem?

HTH,
Lothar

-- 
Lothar Armbrüster       | la_at_oktagramm.de
Hauptstr. 26            | la_at_heptagramm.de
D-65346 Eltville        | lothar.armbruester_at_t-online.de