dogwin dogwin - 2 years ago 70
SQL Question

Select a value that has a greater number of values

Let's say I have a table like this one:

Table T:
1 x | O
2 x | O
3 x | P
4 y | O
5 y | P
6 y | P
7 z | O
8 z | O
9 z | P

I want to select the values in column A that have a greater number of the values in column B.

For example, I want to select x, y, or z if they have more O's then P's.

I've made several attempts, but I honestly can't figure out how to do something like this.

So, how can I write a query that will retrieve what I want?

EDIT: Expected output would be:

1 x
2 z

Answer Source

This sounds like aggregation. Something like this:

select a
from t
group by a
having sum(B = 'O') > sum(b = 'P');
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download