Correlated subquery display count column

Question

I have the following query which works fine:

SELECT c.cust_id, c.cust_type_cd, c.city, count(*) as `count`
FROM customer c
INNER JOIN account a
    ON a.cust_id = c.cust_id
GROUP BY c.cust_id
HAVING `count` = 2;

Result:

+---------+--------------+---------+-------+
| cust_id | cust_type_cd | city    | count |
+---------+--------------+---------+-------+
|       2 | I            | Woburn  |     2 |
|       3 | I            | Quincy  |     2 |
|       6 | I            | Waltham |     2 |
|       8 | I            | Salem   |     2 |
|      10 | B            | Salem   |     2 |
+---------+--------------+---------+-------+

I would like to achieve the same result using a correlated subquery. I have not been able to make a "count" column as show above:

SELECT c.cust_id, c.cust_type_cd, c.city
FROM customer c
WHERE 2 = (
    SELECT COUNT(*)
    FROM account a
    WHERE a.cust_id = c.cust_id
);

Result:

+---------+--------------+---------+
| cust_id | cust_type_cd | city    |
+---------+--------------+---------+
|       2 | I            | Woburn  |
|       3 | I            | Quincy  |
|       6 | I            | Waltham |
|       8 | I            | Salem   |
|      10 | B            | Salem   |
+---------+--------------+---------+

How can I achieve the same result as the one using the INNER JOIN and have a "count" column?

John Woo John Woo · Accepted Answer · 2018-02-20T03:44:21

Not sure why you wanted this but you also need to specify the subquery in the SELECT part,

SELECT c.cust_id, c.cust_type_cd, c.city, (SELECT COUNT(*)
                                              FROM account a
                                              WHERE a.cust_id = c.cust_id) AS `count`
FROM customer c
WHERE 2 = (
    SELECT COUNT(*)
    FROM account a
    WHERE a.cust_id = c.cust_id
);

Correlated subquery display count column

1 Answers