Re: Query how-to

On Thu, Oct 2, 2008 at 1:49 PM, Montaseri <montaseri(at)gmail(dot)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 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