user3872094 user3872094 - 3 months ago 12
SQL Question

Aggregate function exception when I run my query

This is an extension of my previous question(Getting an exception with datetime diff), but this time i've made my

totalbreaktime
to be a
float
and below is my new query.

MERGE Time_Tracker as target using (SELECT USERID, cast(DATEDIFF(second,starttime,endtime)/60.0 as numeric(36,2)) as ColumnWithBreaksCount FROM BreaksTable
where CONVERT(Date, StartTime) = CONVERT(Date, GETDATE()) GROUP BY USERID) as source ON target.USERID = source.USERID
WHEN MATCHED THEN UPDATE SET BREAKS = source.ColumnWithBreaksCount;


This time when I run this query, I'm getting the below Exception.

Msg 8120, Level 16, State 1, Line 1
Column 'BreaksTable.StartTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'BreaksTable.EndTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


problem:


  1. As discussed in my previous question, I want to update my
    Time_tracker.breaks
    as sum of the
    breakstable.totalbreaktime
    based on the date.

  2. When I run the query(the previous one), it is considering all the data instead of the current date's data.



And my table data is as below.

Time_Tracker

enter image description here

BreaksTable

enter image description here

please let me know where am I going wrong and how can I fix this.

Thanks

Answer

You have this subquery in your query:

SELECT USERID,
       cast(DATEDIFF(second, starttime, endtime)/60.0 as numeric(36,2)) as ColumnWithBreaksCount
FROM BreaksTable 
WHERE CONVERT(Date, StartTime) = CONVERT(Date, GETDATE())
GROUP BY USERID;

The second column is not an aggregated column, nor are the columns in the expression in the GROUP BY clause. Hence the error.

I can speculate that you actually want a SUM() for the second column:

MERGE Time_Tracker as target using
      (SELECT USERID,
              cast(SUM(DATEDIFF(second, starttime, endtime)) / 60.0 as numeric(36,2)) as ColumnWithBreaksCount
       FROM BreaksTable 
       WHERE CONVERT(Date, StartTime) = CONVERT(Date, GETDATE())
       GROUP BY USERID
      ) as source
      ON target.USERID = source.USERID 
WHEN MATCHED THEN UPDATE SET BREAKS = source.ColumnWithBreaksCount;