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: enter image description here

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.

Andriy M · Accepted Answer · 2012-03-18 01:27:34Z

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.

answered Mar 18, 2012 at 1:27

Andriy M

78k18 gold badges100 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sas Over a year ago

that's the prob. By adding ca.amount to group by clause solved the prob. That was a silly mistake. Thanks a lot for helped me out solving the problem

paulsm4 · Accepted Answer · 2012-03-18 01:08:42Z

1

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...

answered Mar 18, 2012 at 1:08

paulsm4

123k23 gold badges175 silver badges245 bronze badges

1 Comment

Sas Over a year ago

I can't drop the group by clause, coz the timesheetID is being used to join the table compAllow with timsheet table. and also tested each one induvidually, everything else seems to be correct.

Collectives™ on Stack Overflow

Getting error on nested query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related