pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSubquery problems
9+ messages / 6 participants
[nested] [flat]
* Subquery problems
@ 1998-08-12 08:15 Jerome Knobl <jknobl@mandanet.ch>
0 siblings, 2 replies; 9+ messages in thread
From: Jerome Knobl @ 1998-08-12 08:15 UTC (permalink / raw)
To: pgsql-sql
Hello,
I'm trying to build some subquery, and it don't works like I want.
I try the following query :
select distinct pseudo, (select count(ref) from questions where
questions.refpseudo=pseudos.refold) as xxx from pseudos;
That query work well on MSAccess, but I have the following error on
postgres :
ERROR: parser: parse error at or near "select"
I must maybe using an other method. I'm want to optain in a simple query
to list some name of my table pseudos and to see the number of occurency
in the table questions.
Thank. Jérôme Knöbl, Switzerland
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Subquery problems
@ 1998-08-12 11:33 Marc Howard Zuckman <marc@fallon.classyad.com>
parent: Jerome Knobl <jknobl@mandanet.ch>
1 sibling, 0 replies; 9+ messages in thread
From: Marc Howard Zuckman @ 1998-08-12 11:33 UTC (permalink / raw)
To: Jerome Knobl <jknobl@mandanet.ch>; +Cc: pgsql-sql
On Wed, 12 Aug 1998, Jerome Knobl wrote:
> Hello,
>
> I'm trying to build some subquery, and it don't works like I want.
>
> I try the following query :
>
> select distinct pseudo, (select count(ref) from questions where
> questions.refpseudo=pseudos.refold) as xxx from pseudos;
>
> That query work well on MSAccess, but I have the following error on
> postgres :
>
> ERROR: parser: parse error at or near "select"
>
> I must maybe using an other method. I'm want to optain in a simple query
> to list some name of my table pseudos and to see the number of occurency
> in the table questions.
>
> Thank. Jérôme Knöbl, Switzerland
>
>
Subqueries in the target clause are not yet implemented in v 6.3.2
Marc Zuckman
marc@fallon.classyad.com
_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_
_ Visit The Home and Condo MarketPlace _
_ http://www.ClassyAd.com _
_ _
_ FREE basic property listings/advertisements and searches. _
_ _
_ Try our premium, yet inexpensive services for a real _
_ selling or buying edge! _
_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Subquery problems
@ 1998-08-12 12:59 Dimitri <dimitri@France.Sun.COM>
parent: Jerome Knobl <jknobl@mandanet.ch>
1 sibling, 0 replies; 9+ messages in thread
From: Dimitri @ 1998-08-12 12:59 UTC (permalink / raw)
To: Jerome Knobl <jknobl@mandanet.ch>; +Cc: pgsql-sql
On Wed, 12 Aug 1998, you wrote:
>Hello,
>
>I'm trying to build some subquery, and it don't works like I want.
>
>I try the following query :
>
>select distinct pseudo, (select count(ref) from questions where
>questions.refpseudo=pseudos.refold) as xxx from pseudos;
You have to try:
select pseudo, count(ref) as xxx
from pseudos, questions
where questions.refpseudo=pseudos.refold
group by pseudo;
or
select pseudo, count(*) as xxx
from pseudos, questions
where questions.refpseudo=pseudos.refold
group by pseudo;
This'll be more correct on SQL point and will be more faster
to execute because you will avoid duplication in your output...
>
>That query work well on MSAccess, but I have the following error on
??? M$ SQL is not a good reference... :))
>postgres :
>
>ERROR: parser: parse error at or near "select"
>
>I must maybe using an other method. I'm want to optain in a simple query
>to list some name of my table pseudos and to see the number of occurency
>in the table questions.
>
>Thank. Jérôme Knöbl, Switzerland
Hope it helps,
(dim)
--
=====================================================
Dimitri KRAVTCHUK (dim) Sun Microsystems
Benchmark Engineer France
dimitri@France.Sun.COM
=====================================================
^ permalink raw reply [nested|flat] 9+ messages in thread
* Subquery problems
@ 2007-06-19 12:17 Ranieri Mazili <ranieri.oliveira@terra.com.br>
0 siblings, 2 replies; 9+ messages in thread
From: Ranieri Mazili @ 2007-06-19 12:17 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-sql
Hello,
I'm having another "problem", I have a function that declares 12
variable, one per month and each them execute a select like bellow:
*DIV_MES01 := (select count(distinct production_date) from production
where extract(month from production_date) = '01' and extract(year from
production_date) = EXTRACT(YEAR FROM current_date));
*Then, I need to check if the variable is equal 0:
*IF DIV_MES01 = 0 THEN
DIV_MES01 := 1;
END IF;
*Finally, I perform the following query:
*SELECT cast(((sum(A.qty_employees_total)
-(sum(A.qty_absence)
-sum(A.qty_vacation)
-sum(A.qty_diseased)
-sum(A.qty_indirect)
-sum(A.qty_transferred))
+sum(A.qty_received))/DIV_MES01) AS integer),
C.id_production_area,
cast('01' as text) AS mes
FROM head_count A, machine B, machine_type C
WHERE EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM
current_date)
AND EXTRACT(MONTH FROM head_count_date) = '01'
AND A.id_machine = B.id_machine
AND B.id_machine_type = C.id_machine_type
GROUP BY C.id_production_area, B.id_machine_type
*Doing it, I need to perform 12 querys united by "UNION", what I want to
do is unify it in only one query, I tryed with the query bellow:
*SELECT date_trunc('month', A.head_count_date)::date as head_date,
cast(((sum(A.qty_employees_total)
-(sum(A.qty_absence)
-sum(A.qty_vacation)
-sum(A.qty_diseased)
-sum(A.qty_indirect)
-sum(A.qty_transferred))
+sum(A.qty_received))/(select count(distinct production_date)
from production
where extract(month from
production_date) = EXTRACT(MONTH FROM date_trunc('month',
A.head_count_date)::date)
and extract(year from
production_date) = EXTRACT(YEAR FROM current_date))) AS integer),
C.id_production_area
FROM head_count A, machine B, machine_type C
WHERE date_trunc('month', A.head_count_date)::date BETWEEN
date_trunc('month', current_date - (EXTRACT(MONTH FROM
current_date)-1) * interval '1 month')::date
AND date_trunc('month', current_date)::date
AND A.id_machine = B.id_machine
AND B.id_machine_type = C.id_machine_type
GROUP BY C.id_production_area, B.id_machine_type, head_count_date,head_date
ORDER BY id_production_area, head_count_date,head_date DESC
*But the results aren't what I want.
What I trying to do is possible?
I appreciate any help.
Thanks
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Subquery problems
@ 2007-06-19 13:40 Merlin Moncure <mmoncure@gmail.com>
parent: Ranieri Mazili <ranieri.oliveira@terra.com.br>
1 sibling, 1 reply; 9+ messages in thread
From: Merlin Moncure @ 2007-06-19 13:40 UTC (permalink / raw)
To: Ranieri Mazili <ranieri.oliveira@terra.com.br>; +Cc: pgsql-general@postgresql.org; pgsql-sql
On 6/19/07, Ranieri Mazili <ranieri.oliveira@terra.com.br> wrote:
>
> Hello,
>
> I'm having another "problem", I have a function that declares 12 variable,
> one per month and each them execute a select like bellow:
> DIV_MES01 := (select count(distinct production_date) from production where
> extract(month from production_date) = '01' and extract(year from
> production_date) = EXTRACT(YEAR FROM current_date));
>
> Then, I need to check if the variable is equal 0:
> IF DIV_MES01 = 0 THEN
> DIV_MES01 := 1;
> END IF;
>
> Finally, I perform the following query:
>
> SELECT cast(((sum(A.qty_employees_total)
> -(sum(A.qty_absence)
> -sum(A.qty_vacation)
> -sum(A.qty_diseased)
> -sum(A.qty_indirect)
> -sum(A.qty_transferred))
> +sum(A.qty_received))/DIV_MES01) AS integer),
> C.id_production_area,
> cast('01' as text) AS mes
> FROM head_count A, machine B, machine_type C
> WHERE EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM
> current_date)
> AND EXTRACT(MONTH FROM head_count_date) = '01'
> AND A.id_machine = B.id_machine
> AND B.id_machine_type = C.id_machine_type
> GROUP BY C.id_production_area, B.id_machine_type
>
> Doing it, I need to perform 12 querys united by "UNION", what I want to do
> is unify it in only one query, I tryed with the query bellow:
>
> SELECT date_trunc('month', A.head_count_date)::date as head_date,
> cast(((sum(A.qty_employees_total)
> -(sum(A.qty_absence)
> -sum(A.qty_vacation)
> -sum(A.qty_diseased)
> -sum(A.qty_indirect)
> -sum(A.qty_transferred))
> +sum(A.qty_received))/(select count(distinct production_date)
> from production
> where extract(month from
> production_date) = EXTRACT(MONTH FROM date_trunc('month',
> A.head_count_date)::date)
> and extract(year from
> production_date) = EXTRACT(YEAR FROM current_date))) AS integer),
> C.id_production_area
> FROM head_count A, machine B, machine_type C
> WHERE date_trunc('month', A.head_count_date)::date BETWEEN
> date_trunc('month', current_date - (EXTRACT(MONTH FROM
> current_date)-1) * interval '1 month')::date
> AND date_trunc('month', current_date)::date
> AND A.id_machine = B.id_machine
> AND B.id_machine_type = C.id_machine_type
> GROUP BY C.id_production_area, B.id_machine_type, head_count_date,head_date
> ORDER BY id_production_area, head_count_date,head_date DESC
>
> But the results aren't what I want.
> What I trying to do is possible?
>
> I appreciate any help.
> Thanks
sure!.
SELECT cast(((sum(A.qty_employees_total)
-(sum(A.qty_absence)
-sum(A.qty_vacation)
-sum(A.qty_diseased)
-sum(A.qty_indirect)
-sum(A.qty_transferred))
+sum(A.qty_received))/DIV_MES01) AS integer),
C.id_production_area,
cast(DIV_MES01 as text) AS mes
FROM head_count A, machine B, machine_type C,
(
select case when ct = 0 then 1 else ct end as DIV_MES01 from
(
select count(distinct production_date) as ctfrom production where
extract(year from production_date) = EXTRACT(YEAR FROM current_date)
) q
) D
WHERE EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM
current_date)
AND EXTRACT(MONTH FROM head_count_date) = DIV_MES01
AND A.id_machine = B.id_machine
AND B.id_machine_type = C.id_machine_type
GROUP BY C.id_production_area, B.id_machine_type;
ok, I didn't syntax check this monster, but it should give you a
start...the trick is to use an 'inline view' to expand your variable
list into a set.
merlin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Subquery problems
@ 2007-06-19 14:59 Ranieri Mazili <ranieri.oliveira@terra.com.br>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Ranieri Mazili @ 2007-06-19 14:59 UTC (permalink / raw)
To: ; +Cc: pgsql-general@postgresql.org; pgsql-sql
-------- Original Message --------
Subject: Re:[GENERAL] Subquery problems
From: Merlin Moncure <mmoncure@gmail.com>
To: Ranieri Mazili <ranieri.oliveira@terra.com.br>
Date: 19/6/2007 10:40
> On 6/19/07, Ranieri Mazili <ranieri.oliveira@terra.com.br> wrote:
>>
>> Hello,
>>
>> I'm having another "problem", I have a function that declares 12
>> variable,
>> one per month and each them execute a select like bellow:
>> DIV_MES01 := (select count(distinct production_date) from production
>> where
>> extract(month from production_date) = '01' and extract(year from
>> production_date) = EXTRACT(YEAR FROM current_date));
>>
>> Then, I need to check if the variable is equal 0:
>> IF DIV_MES01 = 0 THEN
>> DIV_MES01 := 1;
>> END IF;
>>
>> Finally, I perform the following query:
>>
>> SELECT cast(((sum(A.qty_employees_total)
>> -(sum(A.qty_absence)
>> -sum(A.qty_vacation)
>> -sum(A.qty_diseased)
>> -sum(A.qty_indirect)
>> -sum(A.qty_transferred))
>> +sum(A.qty_received))/DIV_MES01) AS integer),
>> C.id_production_area,
>> cast('01' as text) AS mes
>> FROM head_count A, machine B, machine_type C
>> WHERE EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM
>> current_date)
>> AND EXTRACT(MONTH FROM head_count_date) = '01'
>> AND A.id_machine = B.id_machine
>> AND B.id_machine_type = C.id_machine_type
>> GROUP BY C.id_production_area, B.id_machine_type
>>
>> Doing it, I need to perform 12 querys united by "UNION", what I want
>> to do
>> is unify it in only one query, I tryed with the query bellow:
>>
>> SELECT date_trunc('month', A.head_count_date)::date as head_date,
>> cast(((sum(A.qty_employees_total)
>> -(sum(A.qty_absence)
>> -sum(A.qty_vacation)
>> -sum(A.qty_diseased)
>> -sum(A.qty_indirect)
>> -sum(A.qty_transferred))
>> +sum(A.qty_received))/(select count(distinct
>> production_date)
>> from production
>> where extract(month from
>> production_date) = EXTRACT(MONTH FROM date_trunc('month',
>> A.head_count_date)::date)
>> and extract(year from
>> production_date) = EXTRACT(YEAR FROM current_date))) AS integer),
>> C.id_production_area
>> FROM head_count A, machine B, machine_type C
>> WHERE date_trunc('month', A.head_count_date)::date BETWEEN
>> date_trunc('month', current_date - (EXTRACT(MONTH FROM
>> current_date)-1) * interval '1 month')::date
>> AND date_trunc('month', current_date)::date
>> AND A.id_machine = B.id_machine
>> AND B.id_machine_type = C.id_machine_type
>> GROUP BY C.id_production_area, B.id_machine_type,
>> head_count_date,head_date
>> ORDER BY id_production_area, head_count_date,head_date DESC
>>
>> But the results aren't what I want.
>> What I trying to do is possible?
>>
>> I appreciate any help.
>> Thanks
>
> sure!.
>
> SELECT cast(((sum(A.qty_employees_total)
> -(sum(A.qty_absence)
> -sum(A.qty_vacation)
> -sum(A.qty_diseased)
> -sum(A.qty_indirect)
> -sum(A.qty_transferred))
> +sum(A.qty_received))/DIV_MES01) AS integer),
> C.id_production_area,
> cast(DIV_MES01 as text) AS mes
> FROM head_count A, machine B, machine_type C,
> (
> select case when ct = 0 then 1 else ct end as DIV_MES01 from
> (
> select count(distinct production_date) as ctfrom production where
> extract(year from production_date) = EXTRACT(YEAR FROM current_date)
> ) q
> ) D
> WHERE EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM
> current_date)
> AND EXTRACT(MONTH FROM head_count_date) = DIV_MES01
> AND A.id_machine = B.id_machine
> AND B.id_machine_type = C.id_machine_type
> GROUP BY C.id_production_area, B.id_machine_type;
>
> ok, I didn't syntax check this monster, but it should give you a
> start...the trick is to use an 'inline view' to expand your variable
> list into a set.
>
> merlin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>
On this way didn't work, I wanna do only one query to return the data of
entire year, not only one month, but thanks for try.
If someone have an idea of how do it, please, help :D
Thanks
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Subquery problems
@ 2007-06-19 15:24 Merlin Moncure <mmoncure@gmail.com>
parent: Ranieri Mazili <ranieri.oliveira@terra.com.br>
0 siblings, 0 replies; 9+ messages in thread
From: Merlin Moncure @ 2007-06-19 15:24 UTC (permalink / raw)
To: Ranieri Mazili <ranieri.oliveira@terra.com.br>; +Cc: pgsql-general@postgresql.org
On 6/19/07, Ranieri Mazili <ranieri.oliveira@terra.com.br> wrote:
> > sure!.
> >
> > SELECT cast(((sum(A.qty_employees_total)
> > -(sum(A.qty_absence)
> > -sum(A.qty_vacation)
> > -sum(A.qty_diseased)
> > -sum(A.qty_indirect)
> > -sum(A.qty_transferred))
> > +sum(A.qty_received))/DIV_MES01) AS integer),
> > C.id_production_area,
> > cast(DIV_MES01 as text) AS mes
> > FROM head_count A, machine B, machine_type C,
> > (
> > select case when ct = 0 then 1 else ct end as DIV_MES01 from
> > (
> > select count(distinct production_date) as ctfrom production where
> > extract(year from production_date) = EXTRACT(YEAR FROM current_date)
> > ) q
> > ) D
> > WHERE EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM
> > current_date)
> > AND EXTRACT(MONTH FROM head_count_date) = DIV_MES01
> > AND A.id_machine = B.id_machine
> > AND B.id_machine_type = C.id_machine_type
> > GROUP BY C.id_production_area, B.id_machine_type;
> >
> > ok, I didn't syntax check this monster, but it should give you a
> > start...the trick is to use an 'inline view' to expand your variable
> > list into a set.
> >
> On this way didn't work, I wanna do only one query to return the data of
> entire year, not only one month, but thanks for try.
> If someone have an idea of how do it, please, help :D
>
the inlne view (table 'D') breaks down the year month by month. maybe
you have to add DIV_MES01 to the group by?
merlin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Subquery problems
@ 2007-06-21 16:29 Masaru Sugawara <rk73@ghost.plala.or.jp>
parent: Ranieri Mazili <ranieri.oliveira@terra.com.br>
1 sibling, 0 replies; 9+ messages in thread
From: Masaru Sugawara @ 2007-06-21 16:29 UTC (permalink / raw)
To: pgsql-sql
On Tue, 19 Jun 2007 09:17:22 -0300
Ranieri Mazili <ranieri.oliveira@terra.com.br> wrote:
Hi,
This reply is not accurate, but I think there are helpful hints.
--
Masaru Sugawara
select
C.id_production_area,
B.id_machine_type,
A.h_month as month,
max(A.n) as div_mes,
cast((sum(A.qty_employees_total)
-(sum(A.qty_absence)
-sum(A.qty_vacation)
-sum(A.qty_diseased)
-sum(A.qty_indirect)
-sum(A.qty_transferred))
+sum(A.qty_received))/max(A.n) as integer)
from (select *
from
(select *, extract(month from h1.head_count_date) as h_month
from head_count as h1
where extract(year from h1.head_count_date)
= extract(year from current_date)
) as h2,
(select extract(month from production_date) as p_month,
count(distinct p1.production_date) as n
from production as p1
where extract(year from production_date)
= extract(year from current_date)
group by extract(month from production_date)
) as p2
where h2.h_month = p2.p_month
) as A, machine B, machine_type C
where A.id_machine = B.id_machine
AND B.id_machine_type = C.id_machine_type
group by C.id_production_area, B.id_machine_type, A.h_month
order by C.id_production_area, A.h_month, A.h_month DESC;
> Hello,
>
> I'm having another "problem", I have a function that declares 12
> variable, one per month and each them execute a select like bellow:
> *DIV_MES01 := (select count(distinct production_date) from production
> where extract(month from production_date) = '01' and extract(year from
> production_date) = EXTRACT(YEAR FROM current_date));
>
> *Then, I need to check if the variable is equal 0:
> *IF DIV_MES01 = 0 THEN
> DIV_MES01 := 1;
> END IF;
>
> *Finally, I perform the following query:
>
> *SELECT cast(((sum(A.qty_employees_total)
> -(sum(A.qty_absence)
> -sum(A.qty_vacation)
> -sum(A.qty_diseased)
> -sum(A.qty_indirect)
> -sum(A.qty_transferred))
> +sum(A.qty_received))/DIV_MES01) AS integer),
> C.id_production_area,
> cast('01' as text) AS mes
> FROM head_count A, machine B, machine_type C
> WHERE EXTRACT(YEAR FROM head_count_date) = EXTRACT(YEAR FROM
> current_date)
> AND EXTRACT(MONTH FROM head_count_date) = '01'
> AND A.id_machine = B.id_machine
> AND B.id_machine_type = C.id_machine_type
> GROUP BY C.id_production_area, B.id_machine_type
>
> *Doing it, I need to perform 12 querys united by "UNION", what I want to
> do is unify it in only one query, I tryed with the query bellow:
>
> *SELECT date_trunc('month', A.head_count_date)::date as head_date,
> cast(((sum(A.qty_employees_total)
> -(sum(A.qty_absence)
> -sum(A.qty_vacation)
> -sum(A.qty_diseased)
> -sum(A.qty_indirect)
> -sum(A.qty_transferred))
> +sum(A.qty_received))/(select count(distinct production_date)
> from production
> where extract(month from
> production_date) = EXTRACT(MONTH FROM date_trunc('month',
> A.head_count_date)::date)
> and extract(year from
> production_date) = EXTRACT(YEAR FROM current_date))) AS integer),
> C.id_production_area
> FROM head_count A, machine B, machine_type C
> WHERE date_trunc('month', A.head_count_date)::date BETWEEN
> date_trunc('month', current_date - (EXTRACT(MONTH FROM
> current_date)-1) * interval '1 month')::date
> AND date_trunc('month', current_date)::date
> AND A.id_machine = B.id_machine
> AND B.id_machine_type = C.id_machine_type
> GROUP BY C.id_production_area, B.id_machine_type, head_count_date,head_date
> ORDER BY id_production_area, head_count_date,head_date DESC
>
> *But the results aren't what I want.
> What I trying to do is possible?
>
> I appreciate any help.
> Thanks
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Subquery problems
@ 2007-06-23 01:07 Masaru Sugawara <rk73@ghost.plala.or.jp>
0 siblings, 0 replies; 9+ messages in thread
From: Masaru Sugawara @ 2007-06-23 01:07 UTC (permalink / raw)
To: pgsql-sql
On Thu, 21 Jun 2007 14:46:49 -0300
Ranieri Mazili <ranieri.oliveira@terra.com.br> wrote:
Hi,
I noticed that if DIV_MES = 0 (= NULL) then the previous query
didn't go well. I rewrite the query. It's including a check statement
to replace NULL with 1.
At first, you need to create a table of months.
--
Masaru Sugawara
create table tbl_month (p_month integer);
insert into tbl_month values (1);
insert into tbl_month values (2);
insert into tbl_month values (3);
insert into tbl_month values (4);
insert into tbl_month values (5);
insert into tbl_month values (6);
insert into tbl_month values (7);
insert into tbl_month values (8);
insert into tbl_month values (9);
insert into tbl_month values (10);
insert into tbl_month values (11);
insert into tbl_month values (12);
select
C.id_production_area,
B.id_machine_type,
A.h_month as month,
max(A.n) as div_mes,
cast((sum(A.qty_employees_total)
-(sum(A.qty_absence)
-sum(A.qty_vacation)
-sum(A.qty_diseased)
-sum(A.qty_indirect)
-sum(A.qty_transferred))
+sum(A.qty_received))/max(A.n) as integer)
from (select *
from (select *,
extract(month from h1.head_count_date) as h_month
from head_count as h1
where extract(year from h1.head_count_date)
= extract(year from current_date)
) as h2,
(select t1.p_month, coalesce (p1.n, 1) as n
from tbl_month as t1 left outer join
(select extract(month from production_date) as m,
count(distinct p0.production_date) as n
from production as p0
where extract(year from production_date)
= extract(year from current_date)
group by extract(month from production_date)
) as p1 on (t1.p_month = p1.m)
) as p2
where h2.h_month = p2.p_month
) as A, machine B, machine_type C
where A.id_machine = B.id_machine
and B.id_machine_type = C.id_machine_type
group by C.id_production_area, B.id_machine_type, A.h_month
order by C.id_production_area, A.h_month, A.h_month DESC;
> Hello,
>
> Your solution works fine, I would appreciated your prompt reply.
> Thanks a lot
>
>
> -------- Original Message --------
> Subject: Re:[SQL] Subquery problems
> From: Masaru Sugawara <rk73@ghost.plala.or.jp>
> To: Ranieri Mazili <ranieri.oliveira@terra.com.br>
> Date: 21/6/2007 13:25
> > On Tue, 19 Jun 2007 09:17:22 -0300
> > Ranieri Mazili <ranieri.oliveira@terra.com.br> wrote:
> >
> > Hi,
> >
> > This reply is not accurate, but I think there are helpful hints.
> >
> > --
> > Masaru Sugawara
> >
> >
> >
> >
> > select
> > C.id_production_area,
> > B.id_machine_type,
> > A.h_month as month,
> > max(A.n) as div_mes,
> > cast((sum(A.qty_employees_total)
> > -(sum(A.qty_absence)
> > -sum(A.qty_vacation)
> > -sum(A.qty_diseased)
> > -sum(A.qty_indirect)
> > -sum(A.qty_transferred))
> > +sum(A.qty_received))/max(A.n) as integer)
> > from (select *
> > from
> > (select *, extract(month from h1.head_count_date) as h_month
> > from head_count as h1
> > where extract(year from h1.head_count_date)
> > = extract(year from current_date)
> > ) as h2,
> > (select extract(month from production_date) as p_month,
> > count(distinct p1.production_date) as n
> > from production as p1
> > where extract(year from production_date)
> > = extract(year from current_date)
> > group by extract(month from production_date)
> > ) as p2
> > where h2.h_month = p2.p_month
> > ) as A, machine B, machine_type C
> > where A.id_machine = B.id_machine
> > AND B.id_machine_type = C.id_machine_type
> > group by C.id_production_area, B.id_machine_type, A.h_month
> > order by C.id_production_area, A.h_month, A.h_month DESC;
> >
> >
> >
> >
> >
> >
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2007-06-23 01:07 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-08-12 08:15 Subquery problems Jerome Knobl <jknobl@mandanet.ch>
1998-08-12 11:33 ` Marc Howard Zuckman <marc@fallon.classyad.com>
1998-08-12 12:59 ` Dimitri <dimitri@France.Sun.COM>
2007-06-19 12:17 Subquery problems Ranieri Mazili <ranieri.oliveira@terra.com.br>
2007-06-19 13:40 ` Merlin Moncure <mmoncure@gmail.com>
2007-06-19 14:59 ` Ranieri Mazili <ranieri.oliveira@terra.com.br>
2007-06-19 15:24 ` Merlin Moncure <mmoncure@gmail.com>
2007-06-21 16:29 ` Masaru Sugawara <rk73@ghost.plala.or.jp>
2007-06-23 01:07 Re: Subquery problems Masaru Sugawara <rk73@ghost.plala.or.jp>
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