SQL query to count how many values appear N times in a column

I have a table like this, but with 1mi rows:

+------+-----------------+
| TEAM | VISITATION DATE |
+------+-----------------+
|  1   |    02/02/2021   |
|  1   |    04/03/2021   |
|  1   |    10/04/2021   |
|  2   |    13/03/2021   |
|  3   |    01/03/2021   |
|  3   |    27/04/2021   |
|  4   |    21/02/2021   |
|  4   |    14/03/2021   |
|  4   |    01/04/2021   |
+------+-----------------+

And I want to know how many TEAMS were visited N times.

For instance, if I want to know how many TEAMS were visited once, the result would be 1 (because only team 2 was). If i wanted three times, result would be 2 (because both team 1 and 4 were visited three times).

It would be something like

SELECT COUNT(TEAM) 
FROM table 
WHERE COUNT(VISITATION DATE)=1

(then =2, =3, and so on)… But since I can’t use COUNT after WHERE, I don’t know how to do it. Can anyone help?

If it helps, it doesn’t necessarily need to be connected to the visitation date. It could be “How many TEAMS appear once/twice/three times… (only considering the TEAM column)”

Thanks in advance!

Answer

select count(team) from 
(select  team, COUNT(team) as countTeam from t group by team) 
where countTeam = 2

Replace 2 by the number you want.

Demo

Advertisement

Answer