Trumpster - 1 year ago 83
SQL Question

# How to implement "Number of times that Foo has a Bar associated only with that Foo"

My scenario is like this. I have a table like

Foo | Bar | ... |
==================
A | 1 | ... |
A | 2 | ... |
A | 2 | ... |
B | 2 | ... |
B | 2 | ... |
B | 1 | ... |
C | 1 | ... |
C | 1 | ... |
C | 1 | ... |
D | 2 | ... |
D | 1 | ... |
D | 1 | ... |

and what I want a query that does, in English,

"For each
Bar
, get the number of
Foo
s that are associated with that
Bar
only"

So the resulting table would be like

Bar | Count |
=============
1 | 1 |
2 | 0 |

Explanation:

You can see that the rows

C | 1 | ... |
C | 1 | ... |
C | 1 | ... |

are an example where a given
Foo
is associated with only one
Bar
, but there are no other such examples.

This is a generic version of real-life scenario I'm facing and I can't figure out how to solve it.

Hmmm. You only care about foos with only one bar. So that is these:

select foo, min(bar) as bar
from t
group by foo
having min(bar) = max(bar)

Then, if you want the number of bars . . . let me assume you have a list of bars:

select b.bar, coalesce(bb.cnt, 0)
from bars b left join
(select bar, count(*) as cnt
from (select foo, min(bar) as bar
from t
group by foo
) fb
group by bar
) bb
on b.bar = bb.bar;

If you don't have a list of bars, then use (select distinct bar from t) bar instead of bar in the from clause.

I think this will also work:

select bar,
count(distinct case when minbar = maxbar then foo end) as numfoos
from (select t.*, min(bar) over (partition by foo) as minbar,
max(bar) over (partition by foo) as maxbar
from t
) t
group by bar;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download