Received: from www.arcamax.com (arcamax.com [209.96.210.66]) by hub.org (8.10.1/8.10.1) with ESMTP id e5QJ4K100617 for ; Mon, 26 Jun 2000 15:04:20 -0400 (EDT) Received: from bryan2 (bryan [209.96.210.45]) by www.arcamax.com (8.9.3/8.9.3) with SMTP id PAA24001; Mon, 26 Jun 2000 15:04:19 -0400 Message-ID: <00ad01bfdfa1$3d64ad00$2dd260d1@arcamax.com> From: "Bryan White" To: "Ed Loehr" Cc: "pgsql-sql" References: <000b01bfdf8c$aef88000$2dd260d1@arcamax.com> <39578B61.CDAF7D91@austin.rr.com> Subject: Re: A subselect in an aggregate Date: Mon, 26 Jun 2000 15:03:39 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6600 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600 X-Archive-Number: 200006/231 > Bryan White wrote: > > > > This statement works: > > select date, (select sum(qty * price) from orderdetail d where d.orderid = > > orders.orderid) from orders > > > > But when I try to do something like this: > > > > select date, sum(select sum(qty * price) from orderdetail d where d.orderid > > = orders.orderid) from orders group by date > > > > I get ERROR: parser: parse error at or near "select" > > > > Is there a way to apply an agregate function to a subselect like this? > > Avoiding the question, I'm wondering if this simpler form wouldn't be > what you're after? > > select o.date, sum(d.qty * d.price) > from orderdetail d, orders o > where d.orderid = o.orderid > group by o.date This is very slow (acutally I killed it after about 5 minutes): select o.date,sum(d.qty * d.price) from orderdetail d,orders o where o.date = '6/1/2000' group by o.date; Explained: Aggregate (cost=0.00..41074641.24 rows=120300337 width=20) -> Group (cost=0.00..38067132.80 rows=1203003374 width=20) -> Nested Loop (cost=0.00..35059624.37 rows=1203003374 width=20) -> Index Scan using iorddate3 on orders o (cost=0.00..6917.34 rows=1808 width=4) -> Seq Scan on orderdetail d (cost=0.00..12733.78 rows=665378 width=16) Doing a squential scan of orderdetail inside a loop seems to be what kills it. This is quick (it takes a couple of seconds): select o.date,(select sum(od.qty * od.price) from orderdetail od where od.orderid = o.orderid) from orders o where o.date = '6/1/2000'; Explained: Index Scan using iorddate3 on orders o (cost=0.00..6917.34 rows=1808 width=8) SubPlan -> Aggregate (cost=45.24..45.24 rows=1 width=16) -> Index Scan using iodid on orderdetail od (cost=0.00..45.21 rows=11 width=16) I suppose I can do my own grouping on the result data but I would like to avoid that if I could. Which brings me back to the original question: Is there a way to apply an aggregate function to a subselect?