SQL with Group By and SUM

Question

Hello everyone and thanks for the help in advance. I have a SQL statement

		Select Distinct PatientRace,  Count(*) as Count from tblPtMstr1 Where PatientID In 			(Select Distinct MRNumber from tblPatientVisits Where (CheckedOut Between @BeginningDate and @EndingDate)) 			Group By PatientRace Order By Count Desc

I want to create a total row as the last row entry, but can't figure out how to do this, either by SUM or Roll Up. Any help would be appreciated.

Answer 1

Change

Group By PatientRace

to

Group By GROUPING SETS ((PatientRace), ())

To get that row last in the result set, you need to change the ORDER BY clause to

ORDER BY grouping(PatientRace), "Count"

By the way, remove the DISTINCT from the SELECT list. It makes no sense when you already have GROUP BY.

Answer 2

Hi @Kmcnet

As a supplement, you can learn more about 'GROUPING SETS'.

https://www.sqlservertutorial.net/sql-server-basics/sql-server-grouping-sets/

If you can provide some test data, it will help us better help you solve the problem.

Best regards,

Percy Tang