Mark Reyes Mark Reyes - 3 years ago 72
SQL Question

SQL Query for getting the count and Rows with multiple record

I'm trying to figure out the SQL query(MSSQL) for this:

TABLE:

ID AppointmentID TypeID
1 1992 1
2 1992 1
3 1992 2
4 1993 1
...


Result:

AppointmentID TypeCount TypeID
1992 2 1


I am only looking for the AppointmentID of TypeID 1 which got the count of more than 1.

Thanks,

Answer Source

Fiddle

The table we have:

CREATE TABLE appointments
(
    ID INT,
    AppointmentID INT,
    TypeID INT
);

Some data:

INSERT INTO appointments VALUES (1, 1992, 1);
INSERT INTO appointments VALUES (2, 1992, 1);
INSERT INTO appointments VALUES (3, 1992, 2);
INSERT INTO appointments VALUES (4, 1993, 1);

Query:

SELECT AppointmentID, count(*) AS TypeCount, TypeID FROM appointments
WHERE TypeID = 1
GROUP BY AppointmentID, TypeID
HAVING count(*) > 1;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download