Received-SPF: pass (taragoro.hst.terra.com.br: domain of terra.com.br
	designates 200.176.10.9 as permitted sender)
	client-ip=200.176.10.9;
	envelope-from=ranieri.oliveira@terra.com.br; helo=[172.16.2.72];
Message-ID: <4676B5B5.4030102@terra.com.br>
Date: Mon, 18 Jun 2007 13:41:25 -0300
From: Ranieri Mazili <ranieri.oliveira@terra.com.br>
User-Agent: Thunderbird 2.0.0.4 (Windows/20070604)
MIME-Version: 1.0
To: pgsql-general@postgresql.org,  pgsql-sql@postgresql.org
Subject: Re: [SQL] Setting Variable - (Correct)
References: <467697ED.8030506@terra.com.br>
	<108B61B7-DB9A-4FE3-832C-323866956DFE@seespotcode.net>
	<7DA5333D-3437-4033-A222-128E541A04C2@seespotcode.net>
In-Reply-To: <7DA5333D-3437-4033-A222-128E541A04C2@seespotcode.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

-------- 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.