Subquery to get derived value

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

​x
 
SELECT A, B, (SELECT COUNT(A) FROM T WHERE B = A) AS CFROM T​

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

Answer

Use a correlated subquery:

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

 
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

 
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

 
SELECT t1.A, t1.B, COUNT(t2.B) AS cFROM tablename t1 LEFT JOIN tablename t2ON t2.B = t1.AGROUP BY t1.A, t1.B​

Advertisement

Answer