SQL select with two conditions

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

On a table like this, I want to know how many teams that have been visited X times and have medal Y. So far, I can count how many teams where visited X times.

SELECT COUNT(*) AS Team_Visit FROM
(SELECT TEAM FROM table GROUP BY TEAM HAVING COUNT(VISITS)=1);

With this, I know how many teams (it doesn’t matter which ones) where visited once, twice, three times, and so on… But now how do I know, for instance, from the ones that were visited once, how many have medal 1?

An expected result would be:

How many teams, that were visited three times, have medal 2?

The answer would give me 2 (because both teams 1 and 4 were).

Or how many teams, that were visited three times, have medal 3?

The answer would be 1 (because only team 4 was).

Should I use an inner join? Can I do it based on the query that I already have?

Answer

You can use the following. Here is the demo.

with cte as
(
  select 
    team,
    medal,
    count(*) over (partition by team) as total_cnt
  from mytable
)
select
   count(distinct team) as total_teams
from cte
where total_cnt = 3
and medal = 2