Help with hw problem involving a sub query

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

Help with hw problem involving a sub query

Author

Topic

stuckne1
Starting Member

22 Posts

Posted - 2010-10-08 : 09:36:42

All of this data is in one table.

Problem 23...List the names and number of employees supervised(label this value HeadCount) for all the supervisors who supervise more than two employees.

The beginning of what I have that doesn't work...

SELECT Employee_Name FROM
(SELECT COUNT(Employee_Id) AS HeadCount FROM EMPLOYEE_t WHERE
Employee_Id = Employee_Supervisor) tbl;

It says "Employee_Name" doesn't exists...

Here is the table structure

Employee_Id
Employee_Name
Employee_Supervisor

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-08 : 09:54:01

This?

select
e.Employee_Name,
dt.HeadCount
from EMPLOYEE_t as e
join
(select Employee_Supervisor, count(*) as HeadCount from EMPLOYEE_t group by Employee_Supervisor having count(*) > 2) as dt
on e.Employee_Id = dt.Employee_Supervisor

No, you're never too old to Yak'n'Roll if you're too young to die.

stuckne1
Starting Member

22 Posts

Posted - 2010-10-08 : 10:52:50

holy shit you rock.

Yea, that works perfectly, but I'm curious...is there a way to do this using a sub query? Or is this a situation where join is required? Either way thanks!

This is a far as I got with the sub query. Ran but returned nothing...

SELECT DISTINCT Employee_Name FROM
(SELECT COUNT(Employee_Supervisor) AS HeadCount, Employee_Name FROM EMPLOYEE_t WHERE
Employee_Supervisor = Employee_Id GROUP BY Employee_Name) tbl;

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-08 : 11:01:47

I see no other way than my approach because
first you need to identify Employee_Supervisor with "number of employees supervised" > 2
and in the second step you are able to join them via Employee_Id to get the distinct needed values in the select list.

greetings
webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-08 : 11:03:07

quote:
Originally posted by stuckne1
holy shit you rock.

I hope so

I love hard rock and heavy metal...

No, you're never too old to Yak'n'Roll if you're too young to die.

stuckne1
Starting Member

22 Posts

Posted - 2010-10-08 : 11:05:39

quote:
Originally posted by webfred

I see no other way than my approach because
first you need to identify Employee_Supervisor with "number of employees supervised" > 2
and in the second step you are able to join them via Employee_Id to get the distinct needed values in the select list.

greetings
webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Okay, awesome!

stuckne1
Starting Member

22 Posts

Posted - 2010-10-08 : 11:06:39

quote:
Originally posted by webfred

quote:
Originally posted by stuckne1
holy shit you rock.

I hope so
I love hard rock and heavy metal...

No, you're never too old to Yak'n'Roll if you're too young to die.

I listen to that too, good choices!

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-10-08 : 16:02:02

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. Please learn to 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.

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 called an adjacency list model and it has huge problems. Look up the nested sets model for trees & hierarchies. Is this what you meant?

CREATE TABLE Personnel
(emp_id INTEGER NOT NULL PRIMARY KEY,
emp_name VARCHAR(35) NOT NULL
supervisor_emp_id INTEGER
REFERENCES Personnel (emp_id));

>> List the names and number of emps supervised(label this value HeadCount) for all the supervisors who supervise more than two emps. <<

The spec is too vague. IF A supervises B and B suprvises C, is Mr. A's headcount 1 or 2?

SELECT supervisor_emp_id, COUNT (emp_id) AS immediate_head_cnt
FROM Personnel
GROUP BY supervisor_emp_id;

Does your school expel students who get other people to do their homework?

--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

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-08 : 17:02:37

quote:
Originally posted by jcelko

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. Please learn to 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.

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 called an adjacency list model and it has huge problems. Look up the nested sets model for trees & hierarchies. Is this what you meant?

CREATE TABLE Personnel
(emp_id INTEGER NOT NULL PRIMARY KEY,
emp_name VARCHAR(35) NOT NULL
supervisor_emp_id INTEGER
REFERENCES Personnel (emp_id));

>> List the names and number of emps supervised(label this value HeadCount) for all the supervisors who supervise more than two emps. <<

The spec is too vague. IF A supervises B and B suprvises C, is Mr. A's headcount 1 or 2?

SELECT supervisor_emp_id, COUNT (emp_id) AS immediate_head_cnt
FROM Personnel
GROUP BY supervisor_emp_id;

Does your school expel students who get other people to do their homework?

--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

Are you as arrogant in your books as you are on the forums?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources