On Thu, Oct 2, 2008 at 1:49 PM, Montaseri <montaseri@gmail.com> wrote:
> I was wondering if you can help me with the following query.
>
> Given table T1 and columns id, start_date, stop_date and status, propose a
> query that reports count of items opened and closed . status is an enum
> including NEW, xxx, xxxx, CLOSED. The first status of an item is NEW (could
> be used in place of start_date)
> For example
>
> Date Opened Closed
> =============================
> yyyy-mm-dd 25 6
> yyyy-mm-dd 0 16
> yyyy-mm-dd 12 0
> etc
> etc
Here is one way using correlated subqueries.
SELECT A."date", ( SELECT COUNT(*) FROM Yourtable AS Y1 WHERE Y1.start_date =
A."date") AS opened, ( SELECT COUNT(*) FROM Yourtable AS Y2 WHERE Y2.end_date = A."date" ) AS
closedFROM ( SELECT start_date AS "date" FROM Yourtable GROUP BY start_date UNION SELECT
end_dateAS "date" FROM Yourtable GROUP BY end_date ) AS A
ORDER BY A."date";
--
Regards,
Richard Broersma Jr.
Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug
