From: Ranieri Mazili <ranieri.oliveira@terra.com.br>
To: pgsql-general@postgresql.org
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Setting Variable - (Correct)
Date: Mon, 18 Jun 2007 13:41:25 -0300
Message-ID: <4676B5B5.4030102@terra.com.br> (raw)
In-Reply-To: <7DA5333D-3437-4033-A222-128E541A04C2@seespotcode.net>
References: <467697ED.8030506@terra.com.br>
<108B61B7-DB9A-4FE3-832C-323866956DFE@seespotcode.net>
<7DA5333D-3437-4033-A222-128E541A04C2@seespotcode.net>
-------- Original Message --------
Subject: Re:[SQL] [GENERAL] Setting Variable - (Correct)
From: Michael Glaesemann <grzm@seespotcode.net>
To: Michael Glaesemann <grzm@seespotcode.net>
Date: 18/6/2007 13:15
>
> On Jun 18, 2007, at 10:17 , Michael Glaesemann wrote:
>
>> Looking over your function, I'm a little confused about what you're
>> trying to do. I'm guessing the (final) result you're trying to get is
>> the number of hours lost for each product per area per month for the
>> three years prior to the provided date.
>
> Or, rather, the number of hours lost per production area per month for
> the three years prior to the provided date.
>
>> SELECT date_trunc('month', production.production_date)::date
>> AS production_period
>> , product.id_production_area
>> , sum(production_hours) as total_production_hours
>> FROM production
>> JOIN product USING (id_product)
>> WHERE lost_hours = 'S'
>> AND date_trunc('month', a.production_date)::date BETWEEN
>> date_trunc('month', ? - 3 * interval '1 year')::date
>> AND date_trunc('month', ?)::date;
>
> Looks like I forgot the GROUP BY clause:
>
> GROUP BY production_period, id_production_area
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>
Thanks a lot for your prompt reply.
You query is perfect for my problem, but I need another thing with it, I
need to return the sum of production_hours of each month of the current
year, and I need to return too the average of the 3 past years, can I do
all in only one query or I need to do a UNION with another query?
More one time, thanks a lot for your help.
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: ranieri.oliveira@terra.com.br, pgsql-general@postgresql.org
Subject: Re: [SQL] Setting Variable - (Correct)
In-Reply-To: <4676B5B5.4030102@terra.com.br>
* 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