March 6, 2020 at 7:48 pm
Msg 8120, Level 16, State 1, Line 2
Column 'TimeReporting.dbo.TimeData.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Query Here:
SELECT ID, EmpNo, Date1, SUM(Time1)
FROM [TimeReporting].[dbo].[TimeData]
WHERE Date1 BETWEEN '2020-03-01' AND '2020-03-08'
AND EmpNo = '8939'
Table Here:
ID bigint Unchecked
EmpNo nvarchar(4) Unchecked
Date1 date Checked
Time1 decimal(18, 2) Checked
StartTime time(0) Checked
EndTime time(0) Checked
JobNo nvarchar(10) Checked
Activity nvarchar(70) Checked
Description nvarchar(50) Checked
Rework bit Checked
Locked bit Checked
Unchecked
Just driving me nuts. Every post I read online about this suggests joined tables. There is only one table!
Steve Anderson
March 6, 2020 at 8:02 pm
You need a GROUP BY
SELECT ID, EmpNo, Date1, SUM(Time1)
FROM [TimeReporting].[dbo].[TimeData]
WHERE Date1 BETWEEN '2020-03-01'
AND '2020-03-08'AND EmpNo = '8939'
GROUP BY ID, EmpNo, Date1
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 6, 2020 at 8:03 pm
Your query has SUM - which requires a GROUP BY and that is missing.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 6, 2020 at 8:55 pm
I suspect you many not need individual dates in the result at all. I also adjusted the range to 7 days rather than 8, as a week seems like a vastly more likely pay range:
SELECT ID, EmpNo, '2020-03-01' AS Pay_Week, SUM(Time1) AS Total_Time
FROM [TimeReporting].[dbo].[TimeData]
WHERE Date1 >= '20200301' AND Date1 < '20200308'
AND EmpNo = '8939'
GROUP BY ID, EmpNo
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply