Re: [SQL] enforcing with unique indexes..
> > ie i want 1 to 1 mapping between group_id and userid so that , there > shud not be a single group_id having more that one kind of userid. > > can it be done with some sort of UNIQUE INDEX? If you want multiple groups but the user should always be the same 1 1 ok 1 1 ok 2 1 ok 3 2 ok 3 3 bad Perhaps you should normalise a bit further with a separate group_user table - you could restrict that to being unique on group then. - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] problem with subqueries
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.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
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.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] problem with subqueries
[EMAIL PROTECTED] writes: > 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 - No surprise, considering the sub-select is going to be evaluated separately for every row of monthcustomer --- and then most of those evaluations will be thrown away by the DISTINCT :-( A straightforward way of reducing the redundant computations would be to do the DISTINCT first: select year,month, (select sum(monthcustomer.number_of_items) from monthcustomer where monthcustomer.year=m.year and monthcustomer.month=m.month) as NumPots from (select distinct year, month from monthcustomer) as m; But it appears to me that you are reinventing the wheel. Isn't this query the equivalent of a grouped aggregation --- viz, select year, month, sum(number_of_items) as NumPots from monthcustomer group by year, month regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] problem with subqueries
On Sat, 5 Oct 2002 [EMAIL PROTECTED] 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?
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
