两个查询总结为单个值 [英] two queries summing into a single value

问题描述

我有两个问题：


SELECT s1.Employeenumber，s1。[Name]，s2.Exceptiondate，code，s2.totalminutes into scratchpad4

来自

（选择不同的雇员，来自Scratchpad1的[姓名]）AS s1

内部加入

（选择从属人数，例外情况，代码，总和（持续时间）总分钟

来自scratchpad3

其中例如@payrollstartdate和@payrollenddate之间的例外情况

group by employeenumber，exceptiondate，code）as s2

ON s1.Employeenumber = s2.Employeenumber

order by exceptiondate asc


和


SELECT [EmployeeNumber]，[Name]，SUM（[Minutes]）AS summinutes，SUM（[Minutes]）/ 60 AS sumhours，

SUM（CASE WHEN Cat = 2 THEN [Minutes] ELSE 0结束）AS specminutes


FROM

（

SELECT [EmployeeNumber]，[Name]，[Dateonly]，[会议纪要] ]，1 AS Cat

来自Scratchpad2

其中dateonly介于@payrollstartdate和@payroll之间结束日期

UNION ALL

SELECT [EmployeeNumber]，[Name]，[ExceptionDate]，[TotalMinutes]，2

来自ScratchPad4
）t

GROUP BY [EmployeeNumber]，[Name]



以及它们正在做的是将值汇总到spec minutes但我要做的是为每种类型的代码求和，无论是ETO，假期，病假时间还是杂项，并为每种代码显示一个值。现在它只是显示规格分钟。在我的第一个查询中，每个查询都有一个代码值。任何人都可以提供解决这个问题的方法吗？


谢谢


Doug

I have two queries:

SELECT s1.Employeenumber, s1.[Name], s2.Exceptiondate, code, s2.totalminutes into scratchpad4
FROM
(select distinct Employeenumber,[Name] from Scratchpad1) AS s1
inner JOIN
(select employeenumber, exceptiondate, code, sum(duration) as totalminutes
from scratchpad3
where exceptiondate between @payrollstartdate And @payrollenddate
group by employeenumber, exceptiondate, code) as s2
ON s1.Employeenumber = s2.Employeenumber
order by exceptiondate asc

and

SELECT [EmployeeNumber],[Name],SUM([Minutes]) AS summinutes,SUM([Minutes])/60 AS sumhours,
SUM(CASE WHEN Cat=2 THEN [Minutes] ELSE 0 END) AS specminutes

FROM
(
SELECT [EmployeeNumber],[Name],[Dateonly],[Minutes],1 AS Cat
FROM Scratchpad2
where dateonly between @payrollstartdate And @payrollenddate
UNION ALL
SELECT [EmployeeNumber],[Name],[ExceptionDate],[TotalMinutes],2
FROM ScratchPad4
)t
GROUP BY [EmployeeNumber],[Name]


and what these are doing is summing up values into "spec minutes" but what I''m looking to do is to sum for each type of code, either ETO, Vacation, Sick Time or Misc and show a value for each. Right now it''s just showing spec minutes. In my first query, there is a code value for each. Can anyone offer a possible way to solve this issue?

Thank you

Doug

推荐答案

你需要按代码分组。

Rabbit，


你的意思是在第一个查询中分组吗？

Rabbit,

Do you mean group by in the first query?

不，您需要在聚合查询中进行分组。

No, you need to group in your aggregate query.

这篇关于两个查询总结为单个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！