Getting the sum of a datediff result

Question

Hi

I have a SQL statement (MS SQL Server 2005) that does a simple calculation of the differences in dates on a few records. I want to return the total/sum of the DATEDIFFs too.

SELECT     (DATEDIFF(day, StartDate, EndDate)+1) AS myTotal
FROM         myTable
WHERE     (Reason = '77000005471247')

How do I get the SUM from myTotal? That is all I want to return.

Thanks in advance

Answer 1

Use the SUM aggregate:

SELECT     SUM(DATEDIFF(day, StartDate, EndDate)+1) AS myTotal
FROM         myTable
WHERE     (Reason = '77000005471247')

Answer 2

If you include any other columns, you'll need to also include a GROUP BY clause

SELECT     AnotherColumn, SUM(DATEDIFF(day, StartDate, EndDate)+1) AS myTotal
FROM         myTable
WHERE      (Reason = '77000005471247')
GROUP BY   AnotherColumn