SQL subquery and count

I want a few columns and only if there are multiple appointment dates on the same day for an employee.

Appointment table

Initial

employeeID	employeename	appointment_date	type
11	James	1/1/2020	office
11	James	1/1/2020	meeting
11	James	1//2020	school
12	Marsha	2/1/2020	office
13	Shane	3/1/2020	school
13	Shane	3/1/2020	office

Expected

employeeID	employeename	appointment_date	type	Count
11	James	1/1/2020	office	3
11	James	1/1/2020	meeting	3
11	James	1//2020	school	3
13	Shane	3/1/2020	school	2
13	Shane	3/1/2020	office	2

Marsha is excluded because there is only 1 appointment for that date

This is what I have so far:

SELECT 
    a.employeeID,
    a.appointment_date,
    a.type,
    (SELECT COUNT(a.employeeID) 
     FROM a.employeeID 
     GROUP BY a.employeeID 
     HAVING COUNT(a.employeeID) > 1)
FROM 
    appointment a
WHERE 
    a.date BETWEEN '1/1/2020' AND '12/31/2020'

Solution

You can use the windowed version of count().

SELECT x.employeeid,
       x.employeename,
       x.appointment_date,
       x.type,
       x.count
       FROM (SELECT a.employeeid,
                    a.employeename,
                    a.appointment_date,
                    a.type,
                    count(*) OVER (PARTITION BY a.employeeid,
                                                a.appointment_date) count
                    FROM appointment a
                    WHERE a.date >= '2020-01-01'
                          AND a.date < '2021-01-01') x
       WHERE x.count > 1;

Notes:

employeename shouldn't be in the table appointment, it's redundant. There should be a table for the employees in which the name (and other details) can be looked up by the ID.
Avoid using BETWEEN for date/times. Use a range that compares the end excluding it. That way you don't have to worry about the query no longer fetching all the right records when the precision is increased.