'How to get number of same id's where the item was found in mysql?

Let's say I have table like this:

some_id date
1 2022-02-01
2 2022-02-02
3 2022-02-03
3 2022-02-04
3 2022-02-05
3 2022-02-06

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.

mysqlsql


Solution 1:[1]

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

Solution 2:[2]

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');

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Salman A
Solution 2 Tim Biegeleisen

Related Questions