CODEWITHSUNDEEP
mysqlsql
ATMathew
ATMathew

Reputation: 12856

Get a count of one column based on another column

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!

Upvotes: 2

Views: 13574

Answers (2)

AaronLS
AaronLS

Reputation: 38365

I think this is what you want.

Select buyer_id, count(*)
From table
Where acct_id in (Select acct_id From table Where buyer_id = 5) --insert buyer_id param here
Group By buyer_id

Upvotes: 1

Chad
Chad

Reputation: 7507

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

Upvotes: 5

Related Questions