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
March 6, 2020 at 8:03 pm
Your query has SUM - which requires a GROUP BY and that is missing.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy