Query Help

Hi All

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

Using this query I am expecting result as

year month totalreported closederrors openerrors fixederrors
2002 1 31 29 2

2002 2 85 60 25

2002 3 16 12 4
2002 5 41 29 12
2002 7 48 48
2002 8 1 1
2002 10 51 51
2002 11 69 15 54
2002 12 8 8

2003 1 9 9

2003 2 38 38

But Retreiving result as

year month totalreported closederrors openerrors fixederrors
2002 1 31 29 2
2002 1 31 29 9
2002 2 85 60 25
2002 2 85 60 38
2002 3 16 12 4
2002 5 41 29 12
2002 7 48 48
2002 8 1 1
2002 10 51 51
2002 11 69 15 54
2002 12 8 8
2003 1 9 29 2
2003 1 9 29 9
2003 2 38 60 25
2003 2 38 60 38

I mean I values retreives across years data is repeating.

May I know how can I get expected results by above query.

Regards,
Sreedhar