Need a Query

[taru]

Hi,

I have a table TEST which has the following data

coupon status
---------- -----------
1000 y
1000 y
1000 n
1001 y
1001 y
1001 n
1003 n
1004 n
1005 n

Explanation
-----------------
Coupon 1000 has 2 y status and 1 n status
Coupon 1001 has 2 y status and 1 n status
Coupon 1003 has 1 n status
Coupon 1004 has 1 n status
Coupon 1005 has 1 n status

I need coupons and count of all y status and n status
BUT IF a coupon has ALREADY BEEN COUNTED IN Y status IT SHOULDNT BE
cOUNTED
AGAIN IN n status

The output will look some thing like ::

coupon count
---------- ---------
1000 2
1001 2
1003 1
1004 1
1004 1

i tried this but it still gives me count of coupons with y

SELECT coupon,count(status) as COUNT
FROM
test c
WHERE
c.='y' GROUP BY coupon
union
SELECT coupon_code,0 as COUNT
FROM test d
WHERE d.='N'
GROUP BY coupon;

output
--------

coupon count
1000 2
1001 2
1000 0 ---------> y counted,so no need
1001 0 ---------> y counted,so no need
1003 0
1004 0
1005 0

Sorry for the loong post
thanks in advance

[taru]

I tried this in IBM DB2 and it works but not in mysql.

SELECT coupon,count(status) as COUNT
FROM
test c
WHERE
c.status='y' GROUP BY coupon
union
SELECT coupon,0 as COUNT
FROM test d
WHERE d.status='N' and
coupon not in (select coupon FROM test where c.status='y' )
GROUP BY coupon;