Get Total Sum with User Sum

Question

SQL Table:

UserId ReportsRead
  1        4
  2        6
  3        5

I would like to query that table so that I can get the following out:

UserId ReportsRead TotalReports
   1       4           15

The problem is that because I apply the WHERE clause the sum I get will be the same as users reports read.

SELECT UserId, ReportsRead, SUM(ReportsRead) AS TotalReports FROM MyTable WHERE UserId = 1

Is there a built in function that will allow me to do this? I would like to avoid Sub-queries entirely.

Answer 1

I don't usually recommend subqueries in this situation, but in this case, it seems like a simple approach:

SELECT UserId, ReportsRead,
       (SELECT SUM(ReportsRead) from MyTable) AS TotalReports
FROM MyTable
WHERE UserId = 1;

If you want rows for all users, then window functions are the way to go:

select t.*, sum(reportsread) over () as totalreports
from mytable;

However, you can't include a where clause and still expect to get the correct total.

Answer 2

Use the sum window function.

SELECT UserId, ReportsRead, SUM(ReportsRead) OVER() AS TotalReports 
FROM MyTable

Use a filtering condition to get a specific userId like

SELECT * 
FROM (SELECT UserId, ReportsRead, SUM(ReportsRead) OVER() AS TotalReports 
      FROM MyTable
     ) t
WHERE UserId=1