Eliminate group of NULLs from result set

Question

Hi,

I have a query that returns a result set similar to the one below:

   Quarter  | Count
   -------  | -----
1   Q2 2009 | NULL
2   Q2 2009 | NULL
3   Q2 2009 | NULL
4   Q3 2009 | 26
5   Q3 2009 | 27
6   Q3 2009 | NULL

I don't want rows 1-3, because their combined Quarter-Count is NULL. But I do want rows 3-6 because at least one Count from the same Quarter is not NULL.

How do I come from the result set above to this one:

   Quarter  | Count
   -------  | -----
1   Q3 2009 | 26
2   Q3 2009 | 27
3   Q3 2009 | NULL

Thanks.

Answer 1

SELECT  *
FROM    mytable mo
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    mytable mi
        WHERE   mi.quarter = mo.quarter
                AND mi.count IS NOT NULL
        )

Answer 2

SELECT
    Quarter, Count
FROM
    MyTable M
WHERE
    EXISTS (SELECT *
        FROM MyTable m2
        WHERE m2.Count IS NOT NULL AND m.Quarter = m2.Quarter)

Answer 3

You can take advantage of the fact that aggregate functions ignore null and do something like:

SELECT * FROM myTable m1 inner join 
(
SELECT quarter as q2 from myTable 
GROUP BY quarter
HAVING sum(count) is not null
) as m2 on m1.quarter = m2.q2

Which might have a better execution plan than an EXISTS - it might not.