I have this data in SQL Server
| Name | Nationality | Gender |
|---|---|---|
| Anonymous | Chinese | M |
| Anonymous | Russian | F |
| Anonymous | German | F |
| Anonymous | Chinese | F |
| Anonymous | American | M |
| Anonymous | German | M |
I can return the count of nationalities separated by distinct values by:
SELECT
Nationality,
COUNT(*) AS [Nat.Count]
FROM
[table1]
GROUP BY
Nationality
| Nationality | Nat.Count |
|---|---|
| Chinese | 2 |
| Russian | 1 |
| German | 2 |
| American | 1 |
How can I do it so that I have a "Total" row appended to the bottom (see below)? ...just like AutoSum in Excel?
| Nationality | Nat.Count |
|---|---|
| Chinese | 2 |
| Russian | 1 |
| German | 2 |
| American | 1 |
| Total | 6 |
CodePudding user response：
As an alternative to using a UNION 
CodePudding user response：
Thats normally a front end job... its a bit odd to need to do it as part of your query. But if you have to...
SELECT Nationality, COUNT(*) AS [Nat.Count]
FROM [table1]
GROUP BY Nationality
UNION ALL
SELECT 'Total', COUNT(*)
FROM [table1]