Solved: working with Count in T-SQL SELECT statement

I'm using SQL Server 2019.

I have a table that looks like this:

This is the code to create the table

USE [TestDatabase]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestTable1](
          [ScheduleID] [int] NOT NULL,
          [TestDate] [smalldatetime] NULL,
          [StudentID] [int] NULL,
          [LastName] [varchar](50) NULL,
          [FirstName] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (166488250, CAST(N'2021-08-25T00:00:00' AS SmallDateTime), 12349001, N'Bunny', N'Bugs')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (166488250, CAST(N'2021-08-26T00:00:00' AS SmallDateTime), 12349001, N'Bunny', N'Bugs')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589145, CAST(N'2021-08-25T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589161, CAST(N'2021-08-25T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589169, CAST(N'2021-08-25T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589165, CAST(N'2021-08-26T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589153, CAST(N'2021-08-26T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589157, CAST(N'2021-08-26T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589158, CAST(N'2021-08-27T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589154, CAST(N'2021-08-27T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589146, CAST(N'2021-08-27T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589166, CAST(N'2021-08-27T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589162, CAST(N'2021-08-27T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589170, CAST(N'2021-08-27T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589163, CAST(N'2021-08-30T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589159, CAST(N'2021-08-30T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589167, CAST(N'2021-08-30T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589151, CAST(N'2021-08-30T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589155, CAST(N'2021-08-30T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589143, CAST(N'2021-08-30T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589144, CAST(N'2021-08-31T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589156, CAST(N'2021-08-31T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589152, CAST(N'2021-08-31T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589168, CAST(N'2021-08-31T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589160, CAST(N'2021-08-31T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589164, CAST(N'2021-08-31T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589161, CAST(N'2021-09-01T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589169, CAST(N'2021-09-01T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589145, CAST(N'2021-09-01T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589153, CAST(N'2021-09-02T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589157, CAST(N'2021-09-02T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589165, CAST(N'2021-09-02T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589166, CAST(N'2021-09-03T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589162, CAST(N'2021-09-03T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589170, CAST(N'2021-09-03T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589158, CAST(N'2021-09-03T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589154, CAST(N'2021-09-03T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589146, CAST(N'2021-09-03T00:00:00' AS SmallDateTime), 12349023, N'Duck', N'Daffy')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589147, CAST(N'2021-09-03T00:00:00' AS SmallDateTime), 12349024, N'Marvin', N'Martian')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589148, CAST(N'2021-09-03T00:00:00' AS SmallDateTime), 12349024, N'Marvin', N'Martian')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589153, CAST(N'2021-09-03T00:00:00' AS SmallDateTime), 12349029, N'Foghorn', N'Leghorn')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589158, CAST(N'2021-09-03T00:00:00' AS SmallDateTime), 12349029, N'Foghorn', N'Leghorn')
GO
INSERT [dbo].[TestTable1] ([ScheduleID], [TestDate], [StudentID], [LastName], [FirstName]) VALUES (165589160, CAST(N'2021-09-03T00:00:00' AS SmallDateTime), 12349029, N'Foghorn', N'Leghorn')
GO

Open in new window

If I run this query then it gets sorted by TestDate.

SELECT [ScheduleID]
      ,[TestDate]
      ,[StudentID]
      ,[LastName]
      ,[FirstName]
FROM [TestDatabase].[dbo].[TestTable1]
ORDER BY [TestDate]

Open in new window

So then the result looks like this:

This table show TestDates for when a student took a test.
Some days some students took more than one test.
Other days some students took only one.

If you look at the table…

On 8/25/2021 Daffy Duck took 3 tests.

On 8/25/2021 Bugs Bunny took 1 test.

I am trying to get the count of how many students took a test on each TestDate.

As long as a student took one test that day they get counted for that day.

I am not trying to count the number of test taken.

So then for 8/25/201, the count I want is that two students took a test on this day

My Result Set

The result of my query shook look like this image I created below:

I only really care about the [TestDate] and [Count] columns.
I put the description column in this image to explain what the number in the Count rows meant.

Anyone know how I can get this Count for each TestDate?