From: Merlin Moncure <mmoncure@gmail.com>
To: Ranieri Mazili <ranieri.oliveira@terra.com.br>
Cc: pgsql-general@postgresql.org
Subject: Re: Subquery problems
Date: Tue, 19 Jun 2007 11:24:29 -0400
Message-ID: <b42b73150706190824g5b653258n1de2498866330c08@mail.gmail.com> (raw)
In-Reply-To: <4677EF39.6050904@terra.com.br>
References: <4677C952.9060902@terra.com.br>
<b42b73150706190640m7eb90d6bg1468765876b13665@mail.gmail.com>
<4677EF39.6050904@terra.com.br>
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
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: mmoncure@gmail.com, ranieri.oliveira@terra.com.br, pgsql-general@postgresql.org
Subject: Re: Subquery problems
In-Reply-To: <b42b73150706190824g5b653258n1de2498866330c08@mail.gmail.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