Error:An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list,

Question

 

hi all,

 

 

this is my table structure

 

id                     int

emp_Id             int

emp_checkin    datetime

emp_checkout  datetime

hours_worked   int

 

 

and here is my query,

 

 

SELECT hours_worked from tbl_attendence_log where max(id) in (select max(id) from tbl_attendence_log where emp_id=1)

 

if i run this query i get error as "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."

 

1.how do i get the hours_worked having only the emp_id where the id col has the max value.?

2. how can i update the hours_worked column using a trigger as soon the emp_checkout is updated or inserted ?

 

thansk your ur kindly reply

Answer 1

Hi:

 

Could you give some example data and result which you want?

 

More details for us can make a better help.

Answer 2

 

 

SELECT hours_worked from tbl_attendence_log where id in (select max(id) from tbl_attendence_log where emp_id=1)

Answer 3

hi

i think this link helpful 4 u

http://technet.microsoft.com/en-us/library/ms173260.aspx

Answer 4

select top (1) hours_worked from tbl_attendence order by ID DESC -- hours of worked of max ID

I think you may want to use calculated column for hours worked. 

If you want to use trigger, I think it will be

create trigger trUpdateHoursWorked on tbl_attendence AFTER INSERT, UPDATE 

AS
   update T set hours_worked = datediff(hour, emp_checkin, emp_checkout)

   from tbl_attendence T inner join inserted I ON T.ID = I.ID where I.Checkout IS NOT NULL

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

This is minimal polite behavior on SQL forums. 

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

>> this is my table structure <<

If you had basic Netiquette and knew RDBMS, this is what you would have post instead of being rude. Notice that we do not need a magical generic “id” or a computed column in a correct schema. Also the silly “tbl-” prefix is not only a violation of ISO-11179 rules, but one of the fastest ways to find a bad SQL programmers. 

CREATE TABLE Attendance_Log 
(emp_id CHAR(10) NOT NULL,
 emp_checkin_timestamp DATETIME2(0) 
    DEFAULT CURRENT_TIMESTAMP NOT NULL,
 PRIMARY KEY (emp_id, emp_checkin_timestamp),
 emp_checkout_timestamp DATETIME2(0),
 CHECK (emp_checkin_timestamp < emp_checkout_timestamp));
 

Guessing at the specifications you also did not post 

1.how do I get the most recent hours_worked for each employee? The current session is found with (emp_checkout_timestamp IS NULL)

SELECT emp_id, emp_checkout_timestamp, 
       DATEDIFF(MM,emp_checkin_timestamP, emp_checkout_timestamp)
       AS work_hours
  FROM Attendance_Log AS A1
 WHERE emp_checkout_timestamp 
       = (SELECT MAX( emp_checkout_timestamp)
            FROM Attendance_Log AS A2
           WHERE A1.emp_id = A2.emp_id);


>> how can I update the hours_worked column using a trigger as soon the emp_checkout is updated or inserted ? <<

You do not!! This is SQL and not punch cards; You compute it in a VIEW, query or computed columns. You should write fewer than five triggers in your entire career.

Stop programming until you have a basic education; you have n idea what you are doing. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL