Assist to solve this problem? [modified]

[Mycroft Holmes]

If you need the code written for you then trundle across to rentacoder, we support people who want to learn how to write the code, NOT people who want us to do their work for them.

current1999 wrote:

Your propositions have been tested without success.

Then show us what you have tried and we may be able to help you.

Never underestimate the power of human stupidity RAH

[current1999]

May be you allow others to contribute if you can't soften your words a bit,You footnote speaks for you! cheers

[Mycroft Holmes]

Take note of the vaote applied to the messages in this thread, others are contributing. Also note that you are not getting a response that supplies you with the codz, this should also tell you that you are asking the wrong question or are on the wrong site. We are here to help developers, not supply free services. Either do your work or be prepared to pay someone to do it for you. Oh and if you are doing this for your own entertainment then thank you, I find it entertaining education you.

Never underestimate the power of human stupidity RAH

[Blue_Boy]

try this

select mt.town , sum(mt.CompanySize) as TotalNrOfEmployees,
sum(mt.CompanySize)/(select count(*) from mytable) as AverageEmployeesPerTown,
sum(mt.CompanySize)/(select count(*) from mytable where companysize<20) as AverageEmployeesPerTownLessThen20

from mytable mt
group by mt.town
having (sum(mt.CompanySize) > 20)

order by ( sum(mt.CompanySize)) desc

This will give you result as Town TotalNrOfEmployees AverageEmployeesPerTown AverageEmployeesPerTownLessThen20 Texas 53 8 13 Maryland 49 8 12

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.cacttus.com

[Peter_in_2780]

Agreed. The question screamed "homework" when I looked at it. Don't know (but might guess) whi univoted, but have a reasonably heavy 5.

Software rusts. Simon Stephenson, ca 1994.

[Mycroft Holmes]

Looking at the balance I'd say the sultana just worked out that he can vote, thanks!

Never underestimate the power of human stupidity RAH

[David Skelly]

The question has "homework" written all over it. I notice he did eventually find a sucker willing to do it for him for free.

[Niladri_Biswas]

Hope this helps Input:

Declare @t table(CompanyID int identity, CompanyName varchar(20), Town varchar(20), Country varchar(20), CompanySize int)
insert into @t
select 'CompanyA', 'Maryland', 'USA', 10 union all
select 'CompanyB' , 'Maryland', 'USA', 19 union all
select 'CompanyC', 'Maryland', 'USA', 20 union all
select 'CompanyD' , 'Texas', 'USA', 13 union all
select 'CompanyE', 'Texas', 'USA', 40 union all
select 'CompanyE', 'Florida', 'USA' ,4
Select * from @t

Query:

Select
t2.Town
, t1.[Total Employees In the Town]
, t2.[Avg No of employees(per town)]
, t2.[AVG Less than 20]
from

-- Query1 : The total number of employees in the town
(
Select
[Total Employees In the Town] = SUM(a.CompanySize)
from @t a
join (Select Town From @t Group by Town Having Count(Town) > 1 ) x
on a.Town = x.Town
) t1
left join
(
Select
a.Town
,a.[Avg No of employees(per town)]
,b.[AVG Less than 20]
from
(
-- Query 2: The average number of employees in a company (per town).
Select Town,[Avg No of employees(per town)] = SUM(CompanySize)/COUNT(Town)
From @t
Group by Town Having Count(Town) > 1 ) a

	join (

			-- Query 3 
			--			The average number of employees in a company (per town) only for companies which
			--			have less than 20 employees

			Select a.Town, \[AVG Less than 20\] =  SUM(CompanySize)/Count(a.Town) 
			from @t a 
			join (Select Town From @t Group by Town Having Count(Town) > 1 ) x on a.Town = x.Town
			where a.CompanySize < 20
			group by a.Town) b
			on a.Town = b.Town

) t2 on 1 = 1

Output:

Town Total Employees In the Town Avg No of employees(per town) AVG Less than 20
Maryland 102 16 14
Texas 102 26 13

Thanks

Niladri Biswas

[current1999]

Thanks a million Blue_Boy. It is good there is an exceptional person like you. Some have no clues, only to be throwing DIATRIBES. Thanks a million Blue_Boy for sharing your knowledge. Regards

modified on Monday, July 18, 2011 4:08 AM

[current1999]

Hi Niladri, Your answer and that of Blue_boy have really expanded my thoughts.First I am very grateful. Meanwhile, the results from the two queries are different. The problem lies in the filtering. Sincerely, I am really grateful.I will work further on your posted queries. Regards, Current