Subquery to get derived value

Question

I have the below table T in MySQL, with columns A and B.

I want to query this table to get a value 'C', that has the count of the number of times the value in Column 'A' appears in Column 'B'. For example, 1 appears 2 times in Column B, so the first row of column C should be 2. I don't want to iterate over the table, so I want to get this using subqueries. The desired output is given below.

I tried using the below query

SELECT A, B, (SELECT COUNT(A) FROM T WHERE B = A) AS C
FROM T

But I got 0 for all rows in column C. Please help me identify the mistake.

Answer 1

Use a correlated subquery:

SELECT t1.A, t1.B,
      (SELECT COUNT(*) FROM tablename t2 WHERE t2.B = t1.A) AS C 
FROM tablename t1

Or:

SELECT t1.A, t1.B,
      (SELECT SUM(t2.B = t1.A) FROM tablename t2) AS C 
FROM tablename t1

Or with a self LEFT join and aggregation:

SELECT t1.A, t1.B, COUNT(t2.B) AS c
FROM tablename t1 LEFT JOIN tablename t2
ON t2.B = t1.A
GROUP BY t1.A, t1.B