Display count of records from multiple tables in SQL Server

Hi pandeygolu4200,

Please refer below  sql query.

Sql

CREATE TABLE #tblevent
(
	 [EventId] VARCHAR(100)					 
	,[EventName] VARCHAR(100)  			
	,[CompanyName] VARCHAR(100)            
	,[IsActive]INT
	,[IsDeleted] INT     
	,[CreatedOn] VARCHAR(100)    
	,[CreatedBy] VARCHAR(100)     
	,[UpdatedOn] VARCHAR(100)        
	,[UpdatedBy] VARCHAR(100)  
	,[Delete]    VARCHAR(100)            
	,[DeletedBy] VARCHAR(100)
)

INSERT INTO #tblevent VALUES('6FCBA375-562E-418E-8F03-705B3CEDB5C9','ScheduleEvent','9BA3D991-E21E-4ADF-9136-9556F9A0D98B',1,0,' 2023-01-10 13:31:01.513','NULL','2023-01-10 13:31:01.513','NULL','2023-01-10 13:31:01.513','NULL')
INSERT INTO #tblevent VALUES('13C1C037-257F-4BC4-BA1C-3CDF58F221CB','Schedule Eventlist2','2889CED1-AFCF-437B-93B2-3E9FF55365B4', 1,0,'2023-01-10 13:42:12.977','NULL','2023-01-10 13:42:12.977', ' NULL','2023-01-10 13:42:12.977','NULL')
INSERT INTO #tblevent VALUES('F7F893C4-E192-44CB-A0FB-55FBFBD31A14','Schedule Christmas','2889CED1-AFCF-437B-93B2-3E9FF55365B4',1,0,'2023-01-10 13:45:26.113','NULL','2023-01-10 13:45:26.113','NULL','2023-01-10 13:45:26.113','NULL')

CREATE TABLE #tbleventlocationmapping 
(
	EventlocationId VARCHAR(50)                       
	,[EventId]         VARCHAR(50)                     
	,[locationName]    VARCHAR(50)                        
	,[locationAddress] VARCHAR(50)              
	,[locationCity]    VARCHAR(50) 
	,[RoomNo]  		VARCHAR(50)
	,[EventDate]       VARCHAR(50)        
	,[ClinicStarttime] VARCHAR(50)
	,[ClinicEndtime]   VARCHAR(50)
	,[locationContactPerson] VARCHAR(50)
	,[locationContactEmail] VARCHAR(50)  
	,[SpecialInstructions] VARCHAR(50)
	,[CreatedBy] VARCHAR(50)
	,[CreatedOn] VARCHAR(50)
	,[UpdatedBy] VARCHAR(50)
	,[UpdatedOn] VARCHAR(50)
	,[locationId] VARCHAR(50)
)

INSERT INTO #tbleventlocationmapping VALUES('3622EE68-1E84-4B5A-85A8-8A02BECC532E','6FCBA375-562E-418E-8F03-705B3CEDB5C9','Firstlacation','Dadar','Mumbai','23 217','2023-01-20 08:19:19.340','09:00 AM','09:00 PM','firstconatperson','firstcontactemail@yopmail.com','NA','NULL','2023-01-10 13:31:02.020 ','NULL','NULL','2A011DF6-6627-443E-A34E-C958AE0A73CE')
INSERT INTO #tbleventlocationmapping VALUES('A69722F0-4F53-4E36-90F4-E2B5DF3B6E59','6FCBA375-562E-418E-8F03-705B3CEDB5C9','Secondlocation','DAndheri','Mumbai','23 219','2023-01-15 08:19:19.340','08:00 AM','08:00 PM','secondconatct','secondconta@yopmail.com','NA','NULL', '2023-01-10 13:31:02.020',' NULL','NULL',' CD500780-91CF-4B24-9FA7-15A90B506C34')
INSERT INTO #tbleventlocationmapping VALUES('59C6A50A-C88D-4F2E-9BFF-D533284F67FC','13C1C037-257F-4BC4-BA1C-3CDF58F221CB','Center for spectrum healthcare service','3589 Big Ridge Road SpecsorPort','NY 14559','23 208','2023-01-18 13:37:07.893','10:00 AM','10:00 PM','brideg','bridheuser@yopmail.com','Emergency','NULL','2023-01-10 13:42:13.730','NULL','NULL','750F90B9-11CD-490D-8DA9-6FF2446A8B83')
INSERT INTO #tbleventlocationmapping values('FE743FC5-5B91-4307-AD6D-3BA1A55B0FAE','F7F893C4-E192-44CB-A0FB-55FBFBD31A14','Center for spectrum healthcare service','3589 Big Ridge Road SpecsorPort','NY 14559','23 208','2023-01-01 13:37:07.893','10:00 AM','10:00 PM','brideg','bridheusers@yopmail.com','Emergency','NULL','2023-01-10 13:45:26.133','NULL','NULL','750F90B9-11CD-490D-8DA9-6FF2446A8B83')

CREATE TABLE #tblparticipantappointmentinfo
(
[AppointmentId] VARCHAR(50)                            
,[UserID] VARCHAR(50)                                
,[EventId] VARCHAR(50)                               
,[AppointmentTime] VARCHAR(50)
,[locationId] VARCHAR(50)                             
,[ServiceId] VARCHAR(50)                         
,[IsActive]  INT
,[IsDeleted] INT   
,[CreatedOn] VARCHAR(50)               
,[CreatedBy] VARCHAR(50)  
,[UpdatedOn] VARCHAR(50)           
,[UpdatedBy] VARCHAR(50)   
,[DeletedOn] VARCHAR(50)              
,[DeletedBy] VARCHAR(50)
)

INSERT INTO #tblparticipantappointmentinfo VALUES('4053660A-A40E-4D67-A972-2A577109EB29','01ED141E-470A-46D6-AC70-2ED0D3DE3363','6FCBA375-562E-418E-8F03-705B3CEDB5C9','09:00-09:30 AM','2A011DF6-6627-443E-A34E-C958AE0A73CE','7B65DBD7-4ED1-47EF-93E6-B151BD6D9995','1','0','2023-01-09 12:04:35.733','string','2023-01-09 12:04:35.733','NULL','2023-01-09 12:04:35.733','NULL')
INSERT INTO #tblparticipantappointmentinfo VALUES('18004096-7D54-4D1D-85BD-72626B3AF2F5','3A86E384-468E-422C-95FA-081BAF5183D7','6FCBA375-562E-418E-8F03-705B3CEDB5C9','03:00-03:30 PM','2A011DF6-6627-443E-A34E-C958AE0A73CE','7B65DBD7-4ED1-47EF-93E6-B151BD6D9995','1','0','2023-01-11 05:01:51.880','string','2023-01-11 05:01:51.880','NULL','2023-01-11 07:09:39.427','NULL')
INSERT INTO #tblparticipantappointmentinfo VALUES('170F5B3F-D143-4C94-B40F-FE3666974B4D','C04BD886-3A7E-4C7F-B4FC-C3F21AAB0147','6FCBA375-562E-418E-8F03-705B3CEDB5C9','04:00-04:30 AM','2A011DF6-6627-443E-A34E-C958AE0A73CE','7B65DBD7-4ED1-47EF-93E6-B151BD6D9995','1','0','2023-01-11 06:40:37.977','string','2023-01-11 06:40:37.977','NULL','2023-01-11 06:40:37.977','NULL')

SELECT * FROM #tblevent tblev
SELECT * FROM #tbleventlocationmapping tblloc
SELECT * FROM #tblparticipantappointmentinfo tblpa
SELECT * FROM #temp1 t1
SELECT * FROM #temp2 t2

SELECT locationId,COUNT(tblpa.userId)AS participants INTO #temp1 
FROM #tblparticipantappointmentinfo tblpa GROUP by locationId

SELECT DISTINCT tblloc.locationId INTO #temp2 
FROM #tbleventlocationmapping tblloc

SELECT t2.LocationId, ISNULL(t1.participants, '0') AS participants
FROM #temp2 t2 left join #temp1 t1 ON t1.LocationId = t2.locationId ORDER BY participants DESC

Output