問題描述

我們的員工每周都會收到傭金，由于獎金結構，我必須計算兩個單獨的周薪，然后將兩者相加.

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

這篇關于兩個 select 語句的求和結果的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！