CODEWITHSUNDEEP
sql-serversumgrouping
nextguy
nextguy

Reputation: 15

sql sum a column and also return the last time stamp

I've got a table in SQL Server with several columns. The relevant ones are:

name distance create_date

I have many people identified by name, and every few days they travel a certain distance. For example:

name     distance     create_date
john     15           09/12/2014
john     20           09/22/2014
alex     10           08/15/2014
alex     12           09/05/2014
john     8            09/30/2014
alex     30           09/12/2014

What i would like is a query that for each person returns the sum of distance between two dates, and the create_date of the last entry during that date range, ordered by highest distance DESC. For example, given a date range of 08/01/2014 to 09/25/2014 I would expect this:

name     distance     create_date
alex     52           09/12/2014
john     35           09/22/2014

I thought of trying to do this with a SUM query with a sub query to get the newest date in the range but I think this is not efficient.

Does someone have an idea for this?

Thank you!

Upvotes: 0

Views: 120

Answers (2)

Kiran Hegde
Kiran Hegde

Reputation: 3681

You can use simple sum and max functions for this.

SELECT name,
       SUM(distance) AS distance,
       MAX(create_date) AS create_date
FROM   theTable
WHERE  create_date >= @startDate AND create_date < @endDate
GROUP BY name
ORDER BY distance DESC

Upvotes: 0

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

SELECT name,
       SUM(distance) AS distance,
       MAX(create_date) AS create_date
FROM   Table
WHERE  create_date >= '20140801' AND create_date < '20140925'
GROUP BY name

SQL Fiddle

Upvotes: 1

Related Questions