Group wise record count and total records in SQL Server

Hi satabeach,

Refer below query.

SQL

CREATE table #tblTable(
        PatientId INT,
        ServiceId INT,
        SericeName Nvarchar(50))
 
INSERT INTO #tblTable SELECT 5, 10, 'HB'
INSERT INTO #tblTable SELECT 5, 1, 'ESR'
INSERT INTO #tblTable SELECT 5, 10, 'HB'
INSERT INTO #tblTable SELECT 6, 8,  'Albumin'
INSERT INTO #tblTable SELECT 7, 14, 'Cholestrol'
INSERT INTO #tblTable SELECT 9, 14, 'Cholestrol'
INSERT INTO #tblTable SELECT 10, 1, 'ESR'
INSERT INTO #tblTable SELECT 10, 8, 'Albumin'
INSERT INTO #tblTable SELECT 12, 10, 'HB'
INSERT INTO #tblTable SELECT 18, 8, 'Albumin'

SELECT DISTINCT SericeName,COUNT(PatientId) Total_by_service,(SELECT COUNT(DISTINCT PatientId) FROM #tblTable) Total_Patient
FROM #tblTable
GROUP BY SericeName
 
DROP TABLE #tblTable

Output