Getting error on nested query

Question

I'm trying to develop a time sheet page on my web site, and this is my database stracture:

I want to get total hours(TimeSheetWeeks table's sum(timeFrom-timeTo)), total expense(TimeSheetWeeks's table's total amount) and total(TimSheetWeeks table's total amount+ compAllow table's total Amount).

This is my query I wrote to get my result:

 ;WITH w(tot, tid, eid, fd, td, am, mw) AS
 (
 SELECT   Total = tsw.amount+ca.amount , tsw.[TimeSheetID], [EmployeeID], 
 [FromDate],[ToDate],  tsw.[Amount], SUM(DATEDIFF(MINUTE, [timeFrom],[timeTo] )) 
 FROM
 TimeSheet ts 
 INNER JOIN (
  SELECT  SUM(amount) amount, TimeSheetID 
  FROM TimeSheetWeeks
  GROUP BY TimeSheetID
  )  tsw ON ts.TimeSheetID = tsw.TimeSheetID INNER JOIN (
  SELECT  SUM(amount) amount, TimeSheetID
  FROM CompAllow
  GROUP BY TimeSheetID
  )  ca ON ts.TimeSheetID = ca.TimeSheetID INNER JOIN (
     SELECT  timeFrom, timeTo, TimeSheetID
  FROM TimeSheetWeeks
  ) AS tss ON tss.TimeSheetID=ts.TimeSheetID
  WHERE ts.TimeSheetID=6
  Group By tsw.[TimeSheetID], [EmployeeID], [FromDate], [ToDate], tsw.[Amount]
  )
  SELECT tot, tid, eid, fd, td, Amount = am,  totalHrs = RTRIM(mw/60) + ':' + 
  RIGHT('0'+ RTRIM(mw%60),2)
  FROM w;

this quer causes an error saying

Column 'ca.amount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

What goes wrong here? thanx in advance.

Answer 1

The ca.amount column is referenced in the w CTE, but is neither aggregated nor included in the GROUP BY clause:

 ;WITH w(tot, tid, eid, fd, td, am, mw) AS
 (
 SELECT   Total = tsw.amount+ca.amount , tsw.[TimeSheetID], [EmployeeID], 
 [FromDate],[ToDate],  tsw.[Amount], SUM(DATEDIFF(MINUTE, [timeFrom],[timeTo] )) 
 FROM
 TimeSheet ts 
 INNER JOIN (
  SELECT  SUM(amount) amount, TimeSheetID 
  FROM TimeSheetWeeks
  GROUP BY TimeSheetID
  )  tsw ON ts.TimeSheetID = tsw.TimeSheetID INNER JOIN (
  SELECT  SUM(amount) amount, TimeSheetID
  FROM CompAllow
  GROUP BY TimeSheetID
  )  ca ON ts.TimeSheetID = ca.TimeSheetID INNER JOIN (
     SELECT  timeFrom, timeTo, TimeSheetID
  FROM TimeSheetWeeks
  ) AS tss ON tss.TimeSheetID=ts.TimeSheetID
  WHERE ts.TimeSheetID=6
  Group By tsw.[TimeSheetID], [EmployeeID], [FromDate], [ToDate], tsw.[Amount]
  )
  SELECT tot, tid, eid, fd, td, Amount = am,  totalHrs = RTRIM(mw/60) + ':' + 
  RIGHT('0'+ RTRIM(mw%60),2)
  FROM w;

Either add it to GROUP BY or change the Total expression like this:

Total = tsw.amount + SUM(ca.amount)

depending upon what is the business rule for this query.

Answer 2

The error states the problem:

SELECT  SUM(amount) amount, TimeSheetID
  FROM CompAllow
  GROUP BY TimeSheetID
  )  ca ...

I'm not 100% sure, but my initial recommendation is to drop the "group by" in this clause. At best, it's redundant.

I'd also recommend trying each of the subclauses separately - make sure they're syntactically correct, make sure they're returning expected results.

IMHO...