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: 246960 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, 8 months ago by Phil Parkin.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeffrey Williams SSC Guru Points: 90303 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: 101096 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) "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".