On Thu, Oct 2, 2008 at 1:49 PM, Montaseri <[EMAIL PROTECTED]> 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 closed FROM ( SELECT start_date AS "date" FROM Yourtable GROUP BY start_date UNION SELECT end_date AS "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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql