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