Sum multiple column with PARTITION from single table

user_4877440 0
Viewed 2807

I have a question, it seems simple but I can't figure it out. I have a sample table like this:

Overtime Table (OT)
+----------+------------+----------+-------------+
|EmployeeId|OvertimeDate|HourMargin|OvertimePoint|
+----------+------------+----------+-------------+
|         1|  2020-07-01|     05:00|           15|
|         1|  2020-07-02|     03:00|            9|
|         2|  2020-07-01|     01:00|            3|
|         2|  2020-07-03|     03:00|            9|
|         3|  2020-07-06|     03:00|            9|
|         3|  2020-07-07|     01:00|            3|
+----------+------------+----------+-------------+

OLC Table (OLC)
+----------+------------+-----+------+
|EmployeeId|   OLCDate  | OLC | Trip |
+----------+------------+-----+------+
|         1|  2020-07-01|    2|     0|
|         3|  2020-07-13|    3|     6|
+----------+------------+-----+------+

So, based on that tables, I want to calculate total OT.HourMargin, OT.OTPoint, OLC.OLC, and OLC.Trip with the final result like this:

Result
+----------+-----------+----------+--------+----------+
|EmployeeId|TotalMargin|TotalPoint|TotalOLC|TotalPoint|
+----------+-----------+----------+--------+----------+
|         1|      08:00|        24|       2|         0|
|         2|      04:00|        12|       0|         0|
|         3|      04:00|        24|       3|         6|
+----------+-----------+----------+--------+----------+

Here is the query that I try to achieve the result:

DECLARE @Overtime TABLE (
    EmployeeId      INT,
    OvertimeDate    DATE,
    HourMargin      TIME,
    OvertimePoint   INT
)

DECLARE @OLC TABLE (
    EmployeeId      INT,
    OLCDate         DATE,
    OLC             INT,
    Trip            INT
)

INSERT INTO @Overtime VALUES (1, '2020-07-01', '05:00:00', 15)
INSERT INTO @Overtime VALUES (1, '2020-07-02', '03:00:00', 9)
INSERT INTO @Overtime VALUES (2, '2020-07-01', '01:00:00', 3)
INSERT INTO @Overtime VALUES (2, '2020-07-03', '03:00:00', 9)
INSERT INTO @Overtime VALUES (3, '2020-07-06', '03:00:00', 9)
INSERT INTO @Overtime VALUES (3, '2020-07-07', '01:00:00', 3)

INSERT INTO @OLC VALUES (1, '2020-07-01', 2, 0)
INSERT INTO @OLC VALUES (3, '2020-07-13', 3, 6)

SELECT
    OT.EmployeeId,
    CONVERT(TIME, DATEADD(MS, (SUM(DATEDIFF(MS, '00:00:00.000', OT.HourMargin)) OVER (PARTITION BY OT.EmployeeId)), '00:00:00.000')) AS TotalMargin,
    SUM(OT.OvertimePoint) OVER (PARTITION BY OT.EmployeeId) AS TotalPoint,
    SUM(OLC.OLC) OVER (PARTITION BY OLC.EmployeeId) AS TotalOLC,
    SUM(OLC.Trip) OVER (PARTITION BY OLC.EmployeeId) AS TotalTrip
FROM
    @Overtime OT
    LEFT JOIN @OLC OLC ON OLC.EmployeeId = OT.EmployeeId
                          AND OLC.OLCDate = OT.OvertimeDate
ORDER BY
    EmployeeId

Here is the result from my query:

+----------+-----------+----------+--------+----------+
|EmployeeId|TotalMargin|TotalPoint|TotalOLC|TotalPoint|
+----------+-----------+----------+--------+----------+
|         1|      08:00|        24|    NULL|      NULL|
|         1|      08:00|        24|       2|         0|
|         2|      04:00|        12|    NULL|      NULL|
|         2|      04:00|        12|    NULL|      NULL|
|         3|      04:00|        12|    NULL|      NULL|
|         3|      04:00|        12|    NULL|      NULL|
+----------+-----------+----------+--------+----------+

It seems when I try to SUM multiple columns from single table, it will create multiple rows in the final result. Right now, what came across to my mind is using CTE, separate the multiple column into multiple CTE's and querying from all CTE's. Or even try to create temp table/table variable, query the sum's from each column and store/update it.

So, any idea how to achieve my result without using multiple CTE's or temp tables?

Thank You

window-functions join sum sql-server sql
2 Answers

You want to group together rows that belong to the same EmployeeID, so this implies aggregation rather than window functions:

SELECT
    OT.EmployeeId,
    CONVERT(TIME, DATEADD(MS, SUM(DATEDIFF(MS, '00:00:00.000', OT.HourMargin)), '00:00:00.000')) AS TotalMargin,
    SUM(OT.OvertimePoint) AS TotalPoint,
    COALESCE(SUM(OLC.OLC), 0) AS TotalOLC,
    COALESCE(SUM(OLC.Trip), 0) AS TotalTrip
FROM @Overtime OT
LEFT JOIN @OLC OLC ON OLC.EmployeeId = OT.EmployeeId
GROUP BY OT.EmployeeId

I also don't see the point for the join condition on the dates, so I removed it. Finally, you can use coalesce() to return 0 for rows that have no OLC.

Demo on DB Fiddle:

EmployeeId | TotalMargin | TotalPoint | TotalOLC | TotalTrip
---------: | :---------- | ---------: | -------: | --------:
         1 | 08:00:00    |         24 |        4 |         0
         2 | 04:00:00    |         12 |        0 |         0
         3 | 04:00:00    |         12 |        6 |        12
user_106767160

You've decided to use SUM OVER but you're experiencing the "problem" of multiple rows... that's what a sum over does; you can conceive that doing an OVER(PARTITION..) does a group by that is auto joined back to the driving table so you end up with all the rows from the driving table together with repeated results of the summation

Here is a simple data set:

ProductID, Price
1, 100
1, 200
2, 300
2, 400

Here are some queries and results:

--perform a basic group and sum
SELECT ProductID, SUM(Price) S FROM x GROUP BY ProductID
1, 300
2, 700


--perform basic group/sum and join it back to the main table 
SELECT ProductID, Price, S
FROM
  x 
  INNER JOIN 
  (SELECT ProductID, SUM(Price) s FROM x GROUP BY ProductID) y 
  ON x.ProductID = y.ProductID
1, 100, 300
1, 200, 300
2, 300, 700
2, 400, 700


--perform a sum over, the partition here being the same as the earlier group
SELECT ProductID, Price, SUM(Price) OVER(PARTITION BY ProductID) FROM x
1, 100, 300
1, 200, 300
2, 300, 700
2, 400, 700

You can see the latter two produce the same result, extra rows with the total appended. It may help you understand simple window functions if you conceive that this is what he db does internally - it takes the "partition by", does a subquery group by with it, and joins the results back on whatever columns were in the partition

It looks like what you really want is a simple group:

SELECT
  OT.EmployeeId,
  CONVERT(TIME, DATEADD(MS, (SUM(DATEDIFF(MS, '00:00:00.000', OT.HourMargin))), '00:00:00.000')) AS TotalMargin,
  SUM(OT.OvertimePoint) AS TotalPoint,
  SUM(OLC.OLC) AS TotalOLC,
  SUM(OLC.Trip) AS TotalTrip
FROM @Overtime OT
LEFT JOIN @OLC OLC ON OLC.EmployeeId = OT.EmployeeId
                      AND OLC.OLCDate = OT.OvertimeDate
GROUP BY OT.EmployeeID
user_14106640
Related