Count number of times a value appears in different columns

Question

I have a table of transactions, with fields for buyer and seller. I want a query that outputs a row for each person who has engaged in a transaction, with one column for how many times that person has been a buyer and one for how many times that person has been a seller.

I'm currently achieving the results I want with:

SELECT u.user_id,
     (SELECT COUNT(*) FROM transactions AS b WHERE b.buyer = u.user_id),
     (SELECT COUNT(*) FROM transactions AS s WHERE s.seller = u.user_id)
FROM users AS u;

This works fine, but it strikes me as goofy and unnecessarily slow. And when I start wanting to put conditions -- like, if I want to count how many times a person bought or sold a pencil for less than a dollar between March 1 and March 31 -- then the query gets even goofier.

I'm sure there's a better way, but I've spent a while searching and haven't come up with much. Thanks in advance for your help.

Answer 1

Your query is quite reasonable and quite possibly the fastest way to run this query. You want to be sure that you have two indexes on transactions: transactions(buyer) and transactions(seller).

An alternative method would summarize the data before using explicit joins:

select u.*, b.numbuyer, s.numseller
from users u left join
     (select buyer, count(*) as numbuyer
      from transactions
      group by buyer
     ) b
     on  b.buyer = u.user_id left join
     (select seller, count(*) as numseller
      from transactions
      group by seller
     ) s
     on s.buyer = u.user_id;

However, your query might be the best way to express and run this logic.