How to sum up the datediff result in one? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to sum up the datediff result in one?

Author

Topic

neo_phyte
Starting Member

10 Posts

Posted - 2011-02-18 : 00:45:42

Hello gurus,

I am new on this forum. I am posting here because I am hoping somebody would help me.

I have this query to display all the information, but I had a problem on how to display the datediff result into one.


SELECT dtaPROJECTS.prj_ID, prj_name, 
dtaCLIENTS.clt_ID, clt_companyName, 
dtaCOMPANIES.com_ID, com_name, 
dtaPARTICIPANTS.prt_ID, prt_firstName, prt_lastName,
DATEDIFF(MONTH, dtaPARTICIPANT_SUBSCRIPTIONS.prs_startDate, dtaPARTICIPANT_SUBSCRIPTIONS.prs_endDate) AS monthsLengthOfSubscription
FROM dtaCOMPANIES, dtaCLIENTS, dtaPROJECTS, dtaPARTICIPANTS, dtaPARTICIPANT_SUBSCRIPTIONS
WHERE dtaCOMPANIES.com_ID = dtaCLIENTS.com_ID
AND dtaCOMPANIES.com_deleted = 0
AND dtaCLIENTS.clt_ID = dtaPROJECTS.clt_ID
AND dtaCLIENTS.clt_deleted = 0
AND dtaPROJECTS.prj_ID = dtaPARTICIPANTS.prj_ID
AND dtaPROJECTS.prj_deleted = 0
AND dtaPARTICIPANTS.prt_ID = dtaPARTICIPANT_SUBSCRIPTIONS.prt_ID
AND dtaPARTICIPANTS.prt_deleted = 0

This would display like this:

Joselito Apple 24
Joselito Apple 12
Ramon Kull 12
Ramon Kull 6
Ramon Kull 5

But I want something like this

Joselito Apple 36
Ramon Kull 23

Where it sum up the datediff result and distinct the name of the person. How will I query that based on my existing query.

Thanks,

-neo_phyte

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-18 : 01:40:01

use SUM() with GROUP BY

sample syntax


SELECT col1, SUM( datediff(...) )
FROM   . . . 
GROUP BY col1

KH
[spoiler]Time is always against us[/spoiler]

neo_phyte
Starting Member

10 Posts

Posted - 2011-02-18 : 01:45:02

Problem solved:

SELECT dtaPROJECTS.prj_ID, prj_name,
dtaCLIENTS.clt_ID, clt_companyName,
dtaCOMPANIES.com_ID, com_name,
dtaPARTICIPANTS.prt_ID, prt_firstName, prt_lastName,
SUM(DATEDIFF(MONTH, dtaPARTICIPANT_SUBSCRIPTIONS.prs_startDate, dtaPARTICIPANT_SUBSCRIPTIONS.prs_endDate)) AS monthsLengthOfSubscription
FROM dtaCOMPANIES, dtaCLIENTS, dtaPROJECTS, dtaPARTICIPANTS, dtaPARTICIPANT_SUBSCRIPTIONS
WHERE dtaCOMPANIES.com_ID = dtaCLIENTS.com_ID
AND dtaCOMPANIES.com_deleted = 0
AND dtaCLIENTS.clt_ID = dtaPROJECTS.clt_ID
AND dtaCLIENTS.clt_deleted = 0
AND dtaPROJECTS.prj_ID = dtaPARTICIPANTS.prj_ID
AND dtaPROJECTS.prj_deleted = 0
AND dtaPARTICIPANTS.prt_ID = dtaPARTICIPANT_SUBSCRIPTIONS.prt_ID
AND dtaPARTICIPANTS.prt_deleted = 0
GROUP BY dtaCOMPANIES.com_ID, com_name, dtaCLIENTS.clt_ID, clt_companyName, dtaPROJECTS.prj_ID, prj_name, dtaPARTICIPANTS.prt_ID, prt_lastName, prt_firstName
ORDER BY dtaPARTICIPANTS.prt_ID, prt_lastName, prt_firstName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources