Link to home
Create AccountLog in
Avatar of maqskywalker
maqskywalker

asked on

working with Count in T-SQL SELECT statement

I'm using SQL Server 2019.


I have a table that looks like this:


User generated image


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.


User generated image


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?



User generated image








ASKER CERTIFIED SOLUTION
Avatar of Anders Ebro (Microsoft MVP)
Anders Ebro (Microsoft MVP)
Flag of Denmark image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of maqskywalker
maqskywalker

ASKER

thanks