Trumpster Trumpster - 2 months ago 11
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.

Answer

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;