Query Help - Mailing list pgsql-general

SELECT TotalErrors.Year, TotalErrors.month, TotalReported, ClosedErrors, OpenErrors, FixedErrors FROM

( SELECT DISTINCT EXTRACT( MON FROM logdate ) AS month, EXTRACT ( YEAR FROM logdate) AS year, COUNT(tblInformation.Inforid ) AS TotalReported

FROM tblInformation

INNER JOIN tblError ON tblInformation.inforid = tblError.inforid

INNER JOIN tblErrorStatus ON tblErrorStatus.statusid = tblError.statusid

INNER JOIN tblJobInformation ON tblJobInformation.inforid = tblInformation.inforid

WHERE logdate >= '2002-01-01' AND logdate < now() AND tblJobInformation.projectid = 33

GROUP BY year, month ) AS TotalErrors

LEFT JOIN

( SELECT DISTINCT EXTRACT( MON FROM logdate ) AS month , EXTRACT ( YEAR FROM logdate) AS year, COUNT(tblInformation.Inforid ) AS ClosedErrors

FROM tblInformation

INNER JOIN tblError ON tblInformation.inforid = tblError.inforid

INNER JOIN tblErrorStatus ON tblErrorStatus.statusid = tblError.statusid

INNER JOIN tblJobInformation ON tblJobInformation.inforid = tblInformation.inforid

WHERE logdate >= '2002-01-01' AND logdate < now() AND tblError.statusid = 2 AND tblJobInformation.projectid = 33

GROUP BY year, month ) As ClosedErrors

ON TotalErrors.month = ClosedErrors.month

LEFT JOIN

( SELECT DISTINCT EXTRACT( MON FROM logdate ) AS month, EXTRACT ( YEAR FROM logdate) AS year, COUNT(tblInformation.Inforid ) AS OpenErrors

FROM tblInformation

INNER JOIN tblError ON tblInformation.inforid = tblError.inforid

INNER JOIN tblErrorStatus ON tblErrorStatus.statusid = tblError.statusid

INNER JOIN tblJobInformation ON tblJobInformation.inforid = tblInformation.inforid

WHERE logdate >= '2002-01-01' AND logdate < now() AND tblError.statusid = 6 AND tblJobInformation.projectid = 33

GROUP BY year, month ) AS OpenErrors

ON TotalErrors.month = OpenErrors.month

LEFT JOIN

( SELECT DISTINCT EXTRACT( MON FROM logdate ) AS month, EXTRACT ( YEAR FROM logdate) AS year, COUNT(tblInformation.Inforid ) AS FixedErrors

FROM tblInformation

INNER JOIN tblError ON tblInformation.inforid = tblError.inforid

INNER JOIN tblErrorStatus ON tblErrorStatus.statusid = tblError.statusid

INNER JOIN tblJobInformation ON tblJobInformation.inforid = tblInformation.inforid

WHERE logdate >= '2002-01-01' AND logdate < now() AND tblError.statusid = 5 AND tblJobInformation.projectid = 33

GROUP BY year, month ) AS FixedErrors

ON TotalErrors.month = FixedErrors.month

ORDER BY TotalErrors.Year, TotalErrors.month