Getting SUM total - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Getting SUM total

Author

Topic

nigelc
Starting Member

20 Posts

Posted - 2007-01-24 : 11:32:32

I have the following query which gives me a list of names, logins, and group name along with a count of how many training modules each individual has sat. This returns 70 rows with each individuals details and totals.

However I want to be able to report instead just the total modules sat for all 70 rows instead of displaying each row individually. This is the query I am using:

SELECT pps_principals.name AS pname, EXT_USER_GROUPS.LOGIN, EXT_USER_GROUPS.NAME,
COUNT(PPS_SCOS.SCO_ID) AS coursecount
FROM PPS_PRINCIPALS
LEFT JOIN PPS_TRANSCRIPTS ON PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID
AND PPS_TRANSCRIPTS.STATUS like '[PCF]'
AND PPS_TRANSCRIPTS.TICKET not like 'l-%'
AND pps_transcripts.date_created between '2006-10-01' and '2007-09-30'
LEFT JOIN PPS_SCOS ON PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_ID
AND pps_scos.name like 'MT%'
LEFT JOIN EXT_USER_GROUPS ON EXT_USER_GROUPS.LOGIN = PPS_PRINCIPALS.LOGIN
WHERE pps_principals.login like '%score%' and ext_user_groups.name like 'ALL SCORE PTY'
AND dbo.PPS_PRINCIPALS.DISABLED IS NULL
GROUP BY pps_principals.name, EXT_USER_GROUPS.LOGIN, EXT_USER_GROUPS.NAME
HAVING COUNT(PPS_SCOS.SCO_ID) > 0
ORDER BY pps_principals.name, EXT_USER_GROUPS.LOGIN, EXT_USER_GROUPS.NAME

I am trying to use SUM to get the overall total but without success so far.

Any help gratefully received.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-24 : 11:36:06

select sum(coursecount)
from
(
YourQueryWithoutOrderBy
) t1

Go with the flow & have fun! Else fight the flow