Query not contained in either an aggregate function or the GROUP BY clause.

Question

Query not contained in either an aggregate function or the GROUP BY clause.

stephen.aa

SSC Eights!

Points: 815
More actions
March 6, 2020 at 7:48 pm

#3732017

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

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

This reply was modified 4 years, 1 month ago by Phil Parkin.

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.

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 2

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

ScottPletcher SSC Guru Points: 100947 More actions · Answer 3

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) A socialist is someone who will give you the shirt off *someone else's* back.