From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: pete@phillipsfamily.freeserve.co.uk
Cc: pgsql-sql@postgresql.org
Subject: Re: problem with subqueries
Date: Sat, 5 Oct 2002 18:02:09 -0700 (PDT)
Message-ID: <20021005175906.R58075-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <200210052219.g95MJVx01588@phillipsfamily.freeserve.co.uk>
On Sat, 5 Oct 2002 pete@phillipsfamily.freeserve.co.uk wrote:
> Because I want to obtain a monthly breakdown, I created a view called
> monthcustomer as this select:
>
> select orders.ord_date, customer.cname,
> date_part('month',orders.ord_date) AS "month",
> date_part('year',orders.ord_date) AS "year",
> orders.number_of_items;
>
> Each month will have multiple numbers of items, so to get a monthly
> breakdown I tried this:
>
> select distinct year,month,
> (select sum(monthcustomer.number_of_items) from monthcustomer where
> monthcustomer.year=m.year and monthcustomer.month=m.month) as NumPots
> from monthcustomer m;
>
> This goes off and never comes back - CPU is hitting the top of the
> chart! I have to ^C to interrupt it, as it runs for ages (I've left
That's going to run that inner select once for every row in monthcustomer
probably.
Would
select year, month, sum(monthcustomer.number_of_items) as NumPots frmo
monthcustomer m group by year, month;
have the same effect, get the sum of the items for each year/month
combination along with which year and month?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, pete@phillipsfamily.freeserve.co.uk
Subject: Re: problem with subqueries
In-Reply-To: <20021005175906.R58075-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox