如何在一个查询中获取特定记录和所有记录的总和?

I Have Table ( emp ):

我有表格(emp):

I want to get First Column

我想得到第一列

DeptNo
DeptNo

Second Column

第二列

SUM of salaries of the employees of each department whose name start with 'A'
各部门员工的工资总额，其名称以“A”开头

and Third column

和第三列

Total Salaries of all the employees of that department
那个部门所有员工的工资总额

I am able to Get first and second Column but unable to get Thiird column with same Query:

我可以得到第一列和第二列，但无法得到同一个查询的Thiird列:

SELECT DeptNo, SUM(Salary) AS 'Name Contains A', SUM(Salary) AS 'Total Salary'
FROM emp
WHERE Ename LIKE 'A%' 
GROUP BY DeptNo

Any Suggestions how can i do this?

有什么建议吗?

4 个解决方案

#1

If your DBMS supports CASE, you can do something like this:

如果你的数据库管理系统支持CASE，你可以这样做:

select 
  deptno, 
  sum(case when ename like 'A%' then salary else 0 end) as ASalary,
  sum(salary) as AllSalaries
from 
  emp
group by 
  deptno

This produces

这个生产

deptno  ASalary AllSalaries 
------  ------- -----------
10       300    500
12       100    100
14         0    500
15       400    700

#2

You can do it with a subquery:

你可以用一个子查询来完成:

SELECT DeptNo, SUM(Salary) AS 'Name Contains A',
       (SELECT SUM(Salary) FROM emp) AS 'Total Salary'
FROM emp
WHERE Ename LIKE 'A%'
GROUP BY DeptNo

#3

To my knowledge, what you are asking is not possible with SQL, but you can get a sum of all the results in the query using the WITH ROLLUP clause as an additional row:

据我所知，您所问的问题在SQL中是不可能的，但是您可以使用with ROLLUP子句作为附加行获得查询中所有结果的总和:

SELECT DeptNo, SUM(Salary)
FROM emp
WHERE Ename LIKE 'A%'
GROUP BY DeptNo WITH ROLLUP

This will give you a grouping of salaries by department and an extra row with a summing of all the salaries for matching departments.

这将给你一个按部门划分的工资分组，以及一个额外的行，其中包含所有相匹配的部门的工资。

#4

You could use a subquery like this:

您可以使用如下子查询:

SELECT emp."DeptNo", SUM(emp."Salary") AS "Name Contains A", sum("DeptTotals"."DeptTotal")
FROM emp inner join (select "DeptNo", sum("Salary") as "DeptTotal"
from emp
group by "DeptNo"
) as "DeptTotals" on "DeptTotals"."DeptNo" = emp."DeptNo"
WHERE emp."Ename" LIKE 'A%' 
GROUP BY emp."DeptNo"

So you join your original query with a subquery which holds the total of all employees of the same dept.

因此，将原始查询与子查询连接起来，子查询包含同一部门所有员工的总数。

#1