Get a count of one column based on another column

mysql, sql

Solution

This should do the trick:

select buyer_id, count(distinct acct_id)
from Table1 a
where exists (
  select * from Table1
  where acct_id = a.acct_id 
    and buyer_id =5)
group by buyer_id

See the sqlfiddle here: http://sqlfiddle.com/#!2/0d3d2/3

Problem

Let's say I have the following data. I am trying to the number a count of each buyer_id by acct_id where the buyer_id contains 5. So for acct 51, which has five instances and contains one with 5, I was to find a count of each buyer_id (excluding 5 of course). This count should go through all acct_id's which contain a buyer_id with 5, and provide a count of all the other buyer_id's. ``` acct_id buyer_id message 51 5 success 51 13 fail 51 4 success 51 6 success 51 9 fail 53 6 fail 53 12 fail 53 4 success 57 6 fail 57 12 fail 57 4 success 57 5 success ``` So in this example I'd end up with something like below ``` buyer_id count(*) 4 2 5 2 6 2 9 1 12 1 13 1 ``` Thanks for your help!

Original source