Count number of times 2 values appear at the same time in two columns

Question

Was able to use: Count number of times value appears in particular column in MySQL

to write:

select 
  ScheduleId,count(*) as s, 
  EmployeeId,count(*) as e 
  from Schedules
  group by ScheduleId, EmployeeId

which returns something like so:

ScheduleId   p       EmployeeId   e
1            1       24242        1
2            1       83928        1
3            1       93829        1
4            2       84993        2
5            1       43434        1

I only want to show the records that have count on both ScheduleId and employeeId > 1 so only show

4            2       84993        2

Iv tried to include this with the query where p.count(*) > 1 etc..

but I cant seem to get it.

Any help please?

Answer 1

You're pretty much there. You just need to filter your results:

SELECT
    ScheduleId, COUNT(*) as s,
    EmployeeId, COUNT(*) as e
FROM
    Schedules
GROUP BY
    ScheduleId, EmployeeId
HAVING
    COUNT(ScheduleId) > 1 AND COUNT(EmployeeId) > 1