CODEWITHSUNDEEP
sqlgroup-bysumaggregate
Erica Stockwell-Alpert
Erica Stockwell-Alpert

Reputation: 4853

SQL - Grouping by one field and summing another field?

I have a database that contains data about page views for items on my site. Each row has a Date, ItemId (guid string), and Views (int). There are many rows with the same ItemId across different dates. I want to aggregate all of these into one row with the sum of the Views. For example:

Date                   |ItemId                               |Views
2017-12-18 20:18:00    |6B3B4A9E-B64B-4BAF-9ABA-42F486CC70AB |1
2017-12-17 20:18:00    |6B3B4A9E-B64B-4BAF-9ABA-42F486CC70AB |3
2017-12-12 20:18:00    |6B3B4A9E-B64B-4BAF-9ABA-42F486CC70AB |5
2017-12-07 20:18:00    |6B3B4A9E-B64B-4BAF-9ABA-42F486CC70AB |1

I'd like to get the following:

ItemId                               | Views
6B3B4A9E-B64B-4BAF-9ABA-42F486CC70AB | 10

Additionally, I need to be able to filter by date, like WHERE [Date] between '12/15/2017' and GetDate()

The Where clause is easy enough, but how do I do the grouping/summing? Keep in mind that I'm not summing just one ItemId, I want to group all rows where the ItemId is the same so that I can get the sum of the views for every item.

Upvotes: 0

Views: 343

Answers (2)

Demo
Demo

Reputation: 476

I think you need a subquery, so that the overall view count is not changed by the WHERE clause restriction. Something such as following:

SELECT
    ItemId,
    sum(Views) as viewCount
FROM table t1
WHERE exists (Select itemid From table t2 Where t2.itemid = t1.itemid and [Date] BETWEEN '2017-12-15' AND GETDATE())
GROUP BY ItemId;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521914

Just use GROUP BY:

SELECT
    ItemId,
    SUM(Views) AS Views
FROM yourTable
WHERE [Date] BETWEEN '2017-12-15' AND GETDATE()
GROUP BY ItemId;

A side note, but here is a link to the MSDN docs describing what are the valid date/time literals for SQL Server. I prefer to use the ISO format I gave above, but what you used m/d/y is also valid.

Upvotes: 2

Related Questions