0

When I run the following code via DataDirect ODBC Driver, it is throwing the

"Column 'dbo.LD.LaborCode' is invalid in the select list because it is not contained in either an aggregate function or a Group By clause."

This has been working fine for quite a while and has now only started throwing this error.

SELECT        LaborCode, SUM(RegHrs + OvtHrs) AS Hours
FROM            dbo.LD
WHERE        (WBS1 IN ('0851.190791.20')) AND (TransDate BETWEEN '1/1/1901' AND '3/27/2019')
GROUP BY LEFT(LaborCode, LEN(LaborCode) - 1)
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
S C
  • 49
  • 6

2 Answers2

1

You would need to repeat the same expression in SELECT statement : 

SELECT LEFT(LaborCode, LEN(LaborCode) - 1) AS LaborCode, 
       SUM(RegHrs + OvtHrs) AS [Hours]
FROM dbo.LD
WHERE WBS1 IN ('0851.190791.20') AND TransDate BETWEEN '1901-01-01' AND '2019-03-27'
GROUP BY LEFT(LaborCode, LEN(LaborCode) - 1);

However, i would use APPLY instead :

SELECT L.LaborCode, SUM(RegHrs + OvtHrs) AS [Hours]
FROM dbo.LD CROSS APPLY
     ( VALUES (LEFT(LaborCode, LEN(LaborCode) - 1)) 
     ) L(LaborCode)
WHERE WBS1 IN ('0851.190791.20') AND TransDate BETWEEN '1901-01-01' AND '2019-03-27'
GROUP BY L.LaborCode;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
  • yep, thank you. I am guessing this part of my code has never ran until now and in reality never did work. Thanks for the help. – S C Mar 27 '19 at 17:13
0
SELECT        LaborCode, SUM(RegHrs + OvtHrs) AS Hours
FROM            dbo.LD
WHERE        (WBS1 IN ('0851.190791.20')) AND (TransDate BETWEEN '1/1/1901' AND 
'3/27/2019')
GROUP BY LaborCode

or this

SELECT   LEFT(LaborCode, LEN(LaborCode) - 1)     LaborCode, SUM(RegHrs + OvtHrs) AS Hours
FROM            dbo.LD
WHERE        (WBS1 IN ('0851.190791.20')) AND (TransDate BETWEEN '1/1/1901' 
AND '3/27/2019')
GROUP BY LEFT(LaborCode, LEN(LaborCode) - 1)
Mohammad Shehroz
  • 226
  • 2
  • 11