Re: Query how-to

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