Assist with this problem - 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

Assist with this problem

Author

Topic

Current199
Starting Member

3 Posts

Posted - 2011-06-25 : 05:27:10

Please can anyone assist me with the following questions?
1. Given the following table (the data could be hundreds of rows):

CompanyID CompanyName Town Country CompanySize
1 CompanyA Maryland USA 10
2 CompanyB Maryland USA 19
3 CompanyC Maryland USA 20
4 CompanyD Texas USA 13
5 CompanyE Texas USA 40
6 CompanyE Florida USA 4

For those towns which have more than one company in them, calculate (in one query):
· The total number of employees in the town.
· The average number of employees in a company (per town).
· The average number of employees in a company (per town) only for companies which
have less than 20 employees.
The results should exclude Florida and be sorted in descending order of the total number of
employees in the town.

I will be very grateful for your professional response.
Regards,
Current

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-25 : 10:30:36

You can use SQL's built in functions SUM and AVG to calculate the sums and averages. To limit it by the criteria you have described, you would need to use WHERE clause and HAVING clause.

There is an example here - http://www.w3schools.com/sql/sql_having.asp - take a look and if that does not get you all the way, post your code and I am sure many people on this forum would be able to offer suggestions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-06-25 : 13:53:23

Adding to Sunita's suggestion you might need to have look at syntax of GROUP BY to get results based on particular groups.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Current199
Starting Member

3 Posts

Posted - 2011-06-25 : 15:44:16

I will be very grateful for anybody who can take little time to attempt the question on his/her side.I have tried using GROUP BY with HAVING without success. This is why I give sample data.
Thanks for your supports.
cheers

Asif5566
Starting Member

9 Posts

Posted - 2011-06-26 : 05:21:33

quote:
Originally posted by Current199

Please can anyone assist me with the following questions?
1. Given the following table (the data could be hundreds of rows):

Hi, Not sure if you've got the answer or not, But this might just help:

SELECT Town,SUM(CompanySize) AS Total_Employee,
CAST(AVG(CompanySize) as Decimal(9)) AS AVG_Employee
FROM test1
GROUP BY Town
HAVING COUNT (CompanyName)>1
ORDER BY Total_Employee DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources