SQL SUM to control DataList item display

first of all is this correct?

you are grouping

 GROUP BY ts.productID, ts.sessionID, ts.trainingCatID, ts.sessionStartDate, ts.sessionEndDate, ts.officeID, tl.officeCity, tl.officeState, tr.module1, tr.module2, tr.module3, tr.module4, tr.module5

but not selecting all (actually, you should group if you use them in select)
anyway...

say your query is q, and you can use this...

select * from (q) x where x.WeekTotal < 60
                                              

1:

oh and if you use my "having" suggestion, remove the " AND WeekTotal < 60" from your where clause

samtran0331, thanks for your suggestion - I was playing with the HAVING clause earlier, but was getting some strange results (or maybe it's just my code that's strange). I've modified my code as you suggested, but am still getting strange results.

Instead of getting one item returned for each training session in the DataList (so that all available training sessions are listed), I'm receiving multiple items for the same training session (almost like it's repeating), but it's only occurring for those training sessions that have registrants. If there are no registrants, then the training session is listed once, as it should be.

Here is what the training registrant table COULD look like. So, for example, I need to find the sum of all modules (1-5) related to all records with the same sessionID. So here, sessionID 28 would have 7 registrants so far, sessionID 37 would have 2 registrants so far, and sessionID 40 would have 9 registrants so far (hopefully this table is formatted correctly once I submit this post).

ID     sessionID          module1     module2     module3     module4     module5
__________________________________________________________
1             28                 0                 0                1                 1                 0
2             28                 1                 1                1                 1                 1
3             37                 1                 1                0                 0                 0
4             40                 1                 1                1                 0                 0
5             40                 0                 0                0                 0                 1
6             40                 1                 1                1                 1                 1

The current modified code is below. THANK YOU for helping me figure this out.

SELECT ts.productID, ts.trainingCatID, ts.sessionStartDate, ts.officeID, ts.sessionID, ts.sessionEndDate, tl.officeCity, tl.OfficeState, isNull(SUM(tr.module1), 0) + isNull(SUM(tr.module2), 0) + isNull(SUM(tr.module3), 0) +isNull(SUM(tr.module4), 0) + isNull(SUM(tr.module5), 0) As WeekTotal
 
FROM TrainingLocations tl, TrainingSessions ts LEFT JOIN TrainingRegistrants tr ON ts.sessionID = tr.sessionID
 
WHERE (ts.productid = @productID AND ts.trainingID = @trainingID AND ts.officeID = tl.officeID AND expDate > @expDate)
 
GROUP BY ts.productID, ts.trainingCatID, ts.sessionStartDate, ts.officeID, ts.sessionID, ts.sessionEndDate, tl.officeCity, tl.officeState, tr.module1, tr.module2, tr.module3, tr.module4, tr.module5 
HAVING (ISNULL(SUM(tr.module1), 0) + ISNULL(SUM(tr.module2), 0) + ISNULL(SUM(tr.module3), 0) + ISNULL(SUM(tr.module4), 0) + ISNULL(SUM(tr.module5), 0)) < 60

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:

Bear with me John0214...it's a bit difficult to fully understand what you need without being able to test with your db myself... but you said:

>>(almost like it's repeating)

Usually when that happens with an aggregate query, it's because there's too many fields in the GROUP BY

Just taking a guess...but try removing the below from your GROUP BY:

, tr.module1, tr.module2, tr.module3, tr.module4, tr.module5

(you shouldn't need to group them if you're summing them)