SQL Query Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Query Issue

Author

Topic

Blug
Starting Member

9 Posts

Posted - 2011-03-30 : 06:02:01

Hey guys, I have a tutorial submission due in 2 days and I was kinda a bit stuck with a query it is asking us to do.

Essentially, there is a table with 4 attributes..

Employees
(ID, Name, Department, Salary)

The query we need to make is, show a list of employee information in which there are 3 or more employees working for a specific department.

So I'm assuming if there are more than 3 department tuples which are the same, then it will list those employees and there details which are related to the name.

I kinda figured I would do something like..

select * from employees where count(department) > 3;

That obviously wouldn't be the correct answer, but I thought I would use count(department) but apparently you can't use that in boolean expressions on sql.

How would I go about this thanks =D

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-30 : 06:07:51

Its a two step process... and aggregates (like count()) should be in the select portion or the 'having' portion...

So

Select A.*
From employees A
Inner Join
(
--identify which departments satisfy '3 or more'
Select Department From employees Group By Department Having count(*)>=3
) B
On A.Department = B.Department

Corey

I Has Returned!!

Blug
Starting Member

9 Posts

Posted - 2011-03-30 : 06:42:44

It's annoying me a bit with this whole select thing, having such a hard time comprehending it. I understand everything besides that. I guess it did take me a while to comprehend the join method as well but meh..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources