0

i have a table T1 which has two columns empname , department

every emp must be in 37 department , but there are some employee who are in less than 37 department .

I need to find the names of emp who are not in all 37 departments.

i'm using a query :

select empname from T1 having count (department) < 37;

but it is not giving me the desired output.

4

Unless you are e.g. counting or summing the entire contents of a table, aggregate functions will require a group by statement. For example, if you are aggregating by counting the number of departments by employee, you must tell it to group by employee

select empname, count(*)
from t1
group by empname
having count(*) < 37
  • great Roka , thanks alot for this. it means i have to add group by clause for this... – Ankit Kapoor Jul 11 '13 at 12:06
  • why the result is not coming without group by claus – Ankit Kapoor Jul 11 '13 at 12:11
  • Unless you are for e.g. counting or summing the entire contents of a table, aggregate functions will require a 'group by' statement. For example, if you are aggregating by counting the number of departments BY employee, you must tell it to GROUP by employee. – RoKa Jul 11 '13 at 12:26
  • one more thing , i have one more table T2 in which department column is also there..now i want to insert only those department in T1.department for those employees whose names are not in all 37 department – Ankit Kapoor Jul 11 '13 at 12:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.