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.




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

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