coalesce help - 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

SQL Server 2005 Forums

Transact-SQL (2005)

coalesce help

Author

Topic

neil_akoga
Yak Posting Veteran

56 Posts

Posted - 2008-10-28 : 05:06:24

Ok I have 2 tables, one lists Training Courses(atblTraining), the other lists bookings for the courses(atblTrainingOrders). I use the following statement to get the data

SELECT c.courseName, c.courseDate, SUM(COALESCE (o.delegateCount, 0)) AS delegateCount, c.courseId, c.placesLeft, c.trainerId
FROM dbo.atblTraining AS c LEFT OUTER JOIN
dbo.atblTrainingOrders AS o ON o.courseID = c.courseId
GROUP BY c.courseName, c.courseDate, c.courseId, c.placesLeft, c.trainerId

I do it like this so that i can show total bookings against a course wether bookings are present or not.

What I want to now do is only bring back sum totals from atblTrainingOrders where the column authorised = 'Authorised' (this column is in atblTrainingOrders).
I've tried putting my where clause before the Group but then it only brings back those with authorised and ignores all the courses with no bookings, so my question is where do i put my where cause, does it have to go in my coalesce function?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-28 : 05:09:45

[code]SELECT c.courseName,
c.courseDate,
SUM(COALESCE(o.delegateCount, 0)) AS delegateCount,
SUM(CASE WHEN o.authorised = 'Authorised' THEN o.delegateCount ELSE 0 END) AS authorisedCount,
c.courseId,
c.placesLeft,
c.trainerId
FROM dbo.atblTraining AS c
LEFT JOIN dbo.atblTrainingOrders AS o ON o.courseID = c.courseId
GROUP BY c.courseName,
c.courseDate,
c.courseId,
c.placesLeft,
c.trainerId[/code]

E 12°55'05.63"
N 56°04'39.26"