Let's say I have table like this:

I want to get the number of rows based on the id where the date was found?

I tried this but it's not working:

SELECT COUNT(id) FROM dates WHERE date = '2022-02-04'

Expected output should be 4 rows since there are 4 same id's where the 2022-02-04 was found.

CodePudding user response：

This should do the job:

SELECT COUNT(*) FROM tbl 
WHERE id IN (
 SELECT id FROM tbl WHERE `date`='2022-02-04'
)

CodePudding user response：

An exists query should do it:

SELECT id, COUNT(*)
FROM t
WHERE EXISTS (
    SELECT 1
    FROM t AS x
    WHERE x.id = t.id
    AND x.date = '2022-02-04'
)
GROUP BY id

CodePudding user response：

Using exists logic we can try:

SELECT COUNT(*)
FROM dates d1
WHERE EXISTS (SELECT 1 FROM dates d2
              WHERE d2.some_id = d1.some_id AND
                    d2.date = '2022-02-04');