Group wise record count and total records in SQL Server

satabeach
 
on Apr 26, 2021 01:25 AM
596 Views

I have table like below

PatientId ServiceId SericeName
5 10 HB
5 1 ESR
5 10 HB
6 8 Albumin
7 14 Cholesterol
9 14 Cholesterol
10 1 ESR
10 8 Albumin
12 10 HB
18 8 Albumin

And the expected result is

SericeName Total_by_service Total_Patient
ESR 2 7
Albumin 3 7
HB 3 7
Cholesterol 2 7

How can I do this in sql

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Apr 27, 2021 04:22 AM

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

SericeName Total_by_service Total_Patient
Albumin 3 7
Cholestrol 2 7
ESR 2 7
HB 3 7