title: Sum multiple column with PARTITION from single table
body: <p>I have a question, it seems simple but I can't figure it out.
  I have a sample table like this:</p>
  <pre><code>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|
  +----------+------------+-----+------+
  </code></pre>
  <p>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:</p>
  <pre><code>Result
  +----------+-----------+----------+--------+----------+
  |EmployeeId|TotalMargin|TotalPoint|TotalOLC|TotalPoint|
  +----------+-----------+----------+--------+----------+
  |         1|      08:00|        24|       2|         0|
  |         2|      04:00|        12|       0|         0|
  |         3|      04:00|        24|       3|         6|
  +----------+-----------+----------+--------+----------+
  </code></pre>
  <p>Here is the query that I try to achieve the result:</p>
  <pre><code>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
  </code></pre>
  <p>Here is the result from my query:</p>
  <pre><code>+----------+-----------+----------+--------+----------+
  |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|
  +----------+-----------+----------+--------+----------+
  </code></pre>
  <p>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.</p>
  <p>So, any idea how to achieve my result without using multiple CTE's or temp tables?</p>
  <p>Thank You</p>