-------- Original Message --------
Subject: Re:[SQL] [GENERAL] Setting Variable - (Correct)
From: Michael Glaesemann <grzm(at)seespotcode(dot)net>
To: Michael Glaesemann <grzm(at)seespotcode(dot)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.