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

  • stephen.aa

    SSChasing Mays

    Points: 600

    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

  • Phil Parkin

    SSC Guru

    Points: 244449

    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 3 months ago by  Phil Parkin.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Jeffrey Williams

    SSC Guru

    Points: 88460

    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

  • ScottPletcher

    SSC Guru

    Points: 98434

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

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

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