nested select statement with SUM

Question

I would like to know if its possible to write a nested select statment?

I have the following which calculates the booked time:

SELECT description, SUM(ts.booked_time) AS booked_time_total, 
CONVERT(VARCHAR(11), @testDate, 106) AS month_name, @week_ref AS week_ref
FROM timesheets ts

WHERE @testDate <= convert(datetime, end_dtm, 120) and 
      dateadd(wk, 1, @testDate) > convert(datetime, start_dtm, 120)

But the booked time appears to be wrong. Isnt the SUM supposed to calculate the total for each row which are within the start_dtm and end_dtm. So if I have 10 rows with 1 in the booked time you would expect the SUM to be 10.

test data:

Answer 1

SUM calculates the total value of the fields while COUNT is the total number of records.

SELECT description, 
       COUNT(ts.booked_time) AS booked_time_total, 
       CONVERT(VARCHAR(11), @testDate, 106) AS month_name, 
       @week_ref AS week_ref
FROM timesheets ts
WHERE @testDate <= convert(datetime, end_dtm, 120) and 
      dateadd(wk, 1, @testDate) > convert(datetime, start_dtm, 120)

Answer 2

I think you are looking to use COUNT rather than SUM.