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.

1 Answer 1

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
8
  • great Roka , thanks alot for this. it means i have to add group by clause for this... Jul 11, 2013 at 12:06
  • why the result is not coming without group by claus Jul 11, 2013 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, 2013 at 12:26
  • More here: dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
    – RoKa
    Jul 11, 2013 at 12:27
  • 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 Jul 11, 2013 at 12:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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