group by and having with multiple clauses

[cshokie]

This is the select statement I have now...

select distinct(float), count(id) from data_table
where float in
(select distinct(float) from data_table
where (
(sd="ab&quot and
( float >= 0.000000 and float <= 200000.000000 )))
group by float,sd HAVING (sd = &quot;ab&quot or (sd = &quot;bc&quot


What I want is a count of all the floats that are
within the given range *and* have an sd or &quot;ab&quot; or &quot;bc&quot;
but not of those with &quot;cd&quot; The above query doesn't
seem quite right, and the answer's not quit right, but
almost. This is what it returns:

float
-------------------- -----------
30.000000 2
50.000000 2
101.123451 1
101.123451 82
300.000000 6
998.000000 2

The problem is that there are two rows for the
101.123451 float (one for those with sd=&quot;ab&quot;
and one for those with sd=&quot;bc&quot I want those
two counts to be one merged count.

Any ideas or advice would be much appreciated!! Thanks!!

 

[carp]

Is it possible that your two rows really ARE distinct if you go beyond six decimal points?

At any rate, I believe your code could be simplified and get you the same results:

select float, count(id) from data_table
WHERE sd IN ('ab','bc')
AND float BETWEEN 0 AND 200000
GROUP BY float;

The DISTINCT isn't required; GROUP BY handles that. Since you are only selecting rows with sd='ab' or sd='bc', you don't need to worry about sd='cd'.