CashCow CashCow - 1 year ago 70
SQL Question

SQL combining of a COUNT with a WHERE in single query

Here is the data, call it table T

-- --
1 14
2 15
3 16
4 1
4 3
4 6
4 9
4 12
4 15

I would like to get the value of A that has only one value and a B value of 15.
There are two rows where B=15 but there are 6 rows where A=4 and only one row where A=2.

So the correct SQL should return me the 2.

I have tried this but it returns both rows.

select A from T group by A,B having Count(A) = 1 and B = 15

This similarly fails:

select A from T where B = 15 group by A having count( A ) = 1

Answer Source

Try this:

select A
from T
group by A
having Count(A) = 1 and Max(B) = 15;

Your problem seems to be that you are grouping by both columns. You only want to group by A.

Admittedly, your query has group by A, T, but I think that is a typo, based on the described behavior.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download