Select a count of record sharing the same value than record X

Posted on

Question :

Figure a table with two columns: id, catId

How to select the records having the same catId than the record having id = 3?

Example:

id catId
1   3
2   4
3   4
4   4
5   3
  1. Input: 3

  2. Then catId of {id = 3} is 4

  3. Then count of records having {catId = 4} is 3

  4. Output: 3 records

Answer :

SELECT COUNT(*)
FROM table t1
JOIN table t2 USING (catId)
WHERE t1.id = 3

If id column is not unique then

SELECT COUNT(DISTINCT t2.id)
FROM table t1
JOIN table t2 USING (catId)
WHERE t1.id = 3

Leave a Reply

Your email address will not be published. Required fields are marked *