Hi
Any help appreciated - I have spent 2 days trying to get this query to
work!
I have an orders database and a customer database.
The orders database has a date field for each order.
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.yearand monthcustomer.month=m.month) as NumPotsfrom 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
this run for 10-20 minutes and it is still running).
I have indexes on the columns involved from the original tables.
Any help appreciated.
Pete
PS: Using pgsql 7.2-70 from Suse distribution.