Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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