Sum results from two select statements

问题:

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.

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)

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.

回答1:

Try this

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

回答2:

Make your query a subquery, and group at the 'superquery' level:

SELECT PerceptionistID, SSNLastFour, sum(CommissionPay), ...
FROM 
(
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)
) AS X
GROUP BY PerceptionistID, SSNLastFour

回答3:

How about modifying [dbo].fnCalculateCommissionForWeekOf or creating a new function that takes a start and end date. This way you can use the same function, and same logic, to run against any span of dates, including the past week, 2 weeks, month, year etc.