CODEWITHSUNDEEP
sqlgroup-bydatediff
Stan
Stan

Reputation: 595

Sum datediff and group by

I have this simple query, but its not producing the results I want... hopefully you can help:

The result is: 

Construction        2
Construction        3
Emergency Funds     4
Housing             5
Seniors Services    9
Seniors Services    185

What I want is:

Construction     5
Emergency Funds  4
Housing          5
Seniors Services 194


SELECT T.NAME, SUM(DATEDIFF (HH,T.DATE_DUE,T.DATE_START))as Donation_Hours FROM TASKS T 
 GROUP BY t.name, T.DATE_DUE,T.DATE_START
 order by name

Upvotes: 0

Views: 5314

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44316

Try this:

SELECT T.NAME, SUM(DATEDIFF(HH,T.DATE_DUE,T.DATE_START))as Donation_Hours 
FROM TASKS T  
GROUP BY t.name
ORDER BY name

Upvotes: 4

Carth
Carth

Reputation: 2343

Stan, some additional detail will go a long way in helping you with this problem. The specific database platform, some sample data, and what you mean when you compare what your result is and what you want it to be, should be included at a minimum.

That being said I think you're alluding to the fact that you have multiple instances of the name rather than a single result per name with a grand total of the hours difference. If this is your problem, you can fix it by removing everything in your group by after the t.name. You're not required to put the constituent elements of an aggregate in the group by clause in the same manner that you would be if you had listed them separately.

I'm assuming that you're using MSSQL through SSMS. This answer was checked against SQL2008 R2.

Upvotes: 0

Related Questions