SQL-How to get the sum of a column using a union-CodePudding

I'm trying to make a query that displays the last name and salary of employees and then the total of the salaries in the row below the salaries. This is what I have right now.

SELECT [Last Name], Salary
FROM [Employee]
UNION
Select [Last Name], Sum(Salary)
FROM [Employee]
GROUP BY [Last Name]

The union doesn't seem to do anything and the only way I can get the sum of the salaries is if it's the only thing I have in the select statement. I'm pretty rusty with SQL so I'm hoping it's an easy solution.

Here is what the output should look like

Last Name    Salary
Johnson      65,000
Anderson     50,000
Smith        70,000
             185,000

CodePudding user response：

The query below your UNION doesn't do much since it simply returns the last names and their salaries based on your data set.

Try this. It retrieves the total of all your employees and puts it in a row called Total.

SELECT [Last Name], Salary
FROM [Employee]
UNION ALL
SELECT 'Total', SUM(salary) from employee

CodePudding user response：

I’d go with

select [Last Name], SUM(Salary)
From [Employee]
Group By Rollup([Last Name])

I think it looks cleaner and you essentially just do one table search.