Add the values of duplicate record in SQL and display it in a report

Question

I am using Microsoft Report Builder to build my reports. I also have a SQL Server database.

I have the following table. Please run the snippet to get a visual representation of my SQL table.

<table style="border:1px solid black;">
  <tr>
    <th>
      Name
    </th>
    <th>
      Timesheet
    </th>
  </tr>
  <tr>
    <td align="center">
      Jacob
    </td>
    <td align="center">
      2
    </td>
  </tr>
  <tr>
    <td align="center">
      Jacob
    </td>
    <td align="center">
      3
    </td>
  </tr>
  <tr>
    <td align="center">
      John
    </td>
    <td align="center">
      1
    </td>
  </tr>
</table>

What I want is to add all the values of the duplicate records in the timesheets column and display the result in the timesheet column with the name in the name column. No duplicate record should be shown.

Please run the following code snippet to get a visual representation of what I want the table to look like based on the previous table

<table style="border:1px solid black;">
  <tr>
    <th>
      Name
    </th>
    <th>
      Timesheet
    </th>
  </tr>
  <tr>
    <td align="center">
      Jacob
    </td>
    <td align="center">
      5
    </td>
  </tr>
  <tr>
    <td align="center">
      John
    </td>
    <td align="center">
      1
    </td>
  </tr>
 </table>

I basically want the duplicate records' timesheets column to be added together. Is there maybe someway I could do this in report builder which will be easier then SQL Server?

Answer 1

Based on the above issues maybe you want like this please check this example.

SQL Query

DECLARE @EMP TABLE
(
    Name VARCHAR(50),
    Timesheet INT
);
INSERT INTO @EMP VALUES('Jacob',2),('Jacob',3),('John',1)

SELECT * FROM @EMP

SELECT Name,SUM(Timesheet) AS Timesheet
FROM @EMP
GROUP BY Name

Output