两个 select 语句的求和结果

2021-09-10 00:00:00 sql tsql sql-server-2008 sql-server

我们的员工每周都会收到佣金，由于奖金结构，我必须计算两个单独的周薪，然后将两者相加.

Our employees are paid commission on a weekly basis and because of a bonus structure i have to calculate two separate weeks of pay and then add the two together.

我有以下 SQL 语句来获取两个单独的星期结果

I have the following SQL statement which gets the two separate weeks results

    SELECT  PerceptionistID, SSNLastFour, CommissionPay,
        PTOPay, HolidayPay, Overtime, TotalPay
    FROM [dbo].fnCalculateCommissionForWeekOf(@MondayOfCurrentWeek)

    UNION

    -- Need to get the following week's data and sum the two together
    SELECT  PerceptionistID, SSNLastFour, CommissionPay,
        PTOPay, HolidayPay, Overtime, TotalPay
    FROM [dbo].fnCalculateCommissionForWeekOf(@MondayOfFollowingWeek)

这为我提供了我需要的数据，但我想将两个结果合并到一个具有相同列的表中，但将一些列添加在一起(CommissionPay、PTOPay、HolidayPay、Overtime、TotalPay).做这个的最好方式是什么?我使用的是 SQL Server 2008 R2.

This gets me the data I need but I would like to combine the two results into one table with the same columns but having some of the columns added together (CommissionPay, PTOPay, HolidayPay, Overtime, TotalPay). What is the best way to do this? I am using SQL Server 2008 R2.

推荐答案

试试这个

SELECT PerceptionistID, SSNLastFour, SUM(CommissionPay) CommissionPay,
        SUM(PTOPay) PTOPay, SUM(HolidayPay) HolidayPay, SUM(Overtime) Overtime, SUM(TotalPay) TotalPay
FROM
(
    SELECT  PerceptionistID, SSNLastFour, CommissionPay,
        PTOPay, HolidayPay, Overtime, TotalPay
    FROM [dbo].fnCalculateCommissionForWeekOf(@MondayOfCurrentWeek)

    UNION ALL

    -- Need to get the following week's data and sum the two together
    SELECT  PerceptionistID, SSNLastFour, CommissionPay,
        PTOPay, HolidayPay, Overtime, TotalPay
    FROM [dbo].fnCalculateCommissionForWeekOf(@MondayOfFollowingWeek)
) t
GROUP BY PerceptionistID, SSNLastFour

相关文章