Multiple COUNTs, different WHEREs

[stewram]

I have a table called 'Fault' which has (amongst others) columns called FaultStartDate, FaultStartTime, FaultEndDate and FaultEndTime. These columns contain standard MySQL DATE or TIME entries (although the end date and times may be NULL).

I want to get a four column table that shows how many fault reports have been opened and closed in the month, i.e. how many faults have a start date & time and how many have an end date and time.

----------------------------------------------
¦ Month ¦ Year ¦ NumberOpened ¦ NumberClosed ¦
----------------------------------------------
¦ Jan ¦ 08 ¦ 5 ¦ 0 ¦
----------------------------------------------

I can get either the NumberOpened OR the NumberClosed but I'm not sure how to combine them. I tried the following:

SELECT DATE_FORMAT(FaultStartDate, '%b') AS Month,
DATE_FORMAT(FaultStartDate, '%y') AS Year,
COUNT(FaultStartDate) AS NumberOpened
FROM Fault
WHERE (MONTH(FaultStartDate = userSelectedDate) AND
YEAR(FaultStartDate = userSelectedDate))
GROUP BY Month
UNION
SELECT DATE_FORMAT(FaultEndDate, '%b') AS Month,
DATE_FORMAT(FaultEndDate, '%y') AS Year,
COUNT(FaultEndDate) AS NumberClosed
FROM Fault
WHERE (MONTH(FaultEndDate = userSelectedDate) AND
YEAR(FaultEndDate = userSelectedDate))
GROUP BY Month

I have tried using JOIN but wasn't sure of the format - as you can tell, I am new to SQL.

The other problem was that whenever there were no start dates then the table would be blank instead of returning zero.

Thanks in advance...

[stewram]

OK, I'm a little further forward. This is where I am now.

This query will be in a JAVA Prepared Statement and the user defined parts will be added at runtime.

On the table, columns 1 & 2 come from the follwing query:
SELECT DATE_FORMAT('user selected date in the form 2008-11-01', '%b') AS Month, SELECT DATE_FORMAT('user selected date in the form 2008-11-01', '%y') AS Year
From Fault
GROUP BY Month

Column 3 comes from the following query:
SELECT COUNT(FaultStartDate) AS NumberOpened
FROM Fault
WHERE (MONTH(FaultStartDate)=(user selected month in the form '11') AND YEAR(FaultStartDate)=(user selected year in the form '2008'))

Column 4 comes from the following query:
SELECT COUNT(FaultEndDate) AS NumberClosed
FROM Fault
WHERE (MONTH(FaultEndDate)=(user selected month in the form '11') AND YEAR(FaultEndDate)=(user selected year in the form '2008'))

The question is how to combine all of the columns so that I just have one query. Can anyone help?

[stewram]

Got it! For Linux anyway...

SELECT DATE_FORMAT('2008-11-01', '%b') AS Month,
DATE_FORMAT('2008-11-01', '%y') AS Year,
(SELECT COUNT(FaultStartDate) FROM Fault WHERE (MONTH(FaultStartDate)=('11') AND YEAR(FaultStartDate)=('2008'))) AS NumberOpened,
(SELECT COUNT(FaultEndDate) FROM Fault WHERE (MONTH(FaultEndDate)=('11') AND YEAR(FaultEndDate)=('2008'))) AS NumberClosed
FROM Fault
GROUP BY Month

This works great and in Linux if there are no faults reported it returns 0 for the appropriate column. However, in XP if there are no faults reported the whole table is null!

Edit... Looks like a MySQL version problem. On the Linux box the MySQL version is 5.0.67. The XP box is looking at a different MySQL server, I've still to get the version number but I believe it's an older one.

[stewram]

A ha... it's not a MySQL server version issue. The Linux box has some test entries in the Fault table, the XP box has no entries in the Fault table yet.

That is the problem... if the table is empty you get a null resultset. If the table has entries but they are filtered out by the WHERE command you get a COUNT of 0.

So the next question is how do I get the null resultset to give me zero's for the COUNT?