Database

current1999 wrote:

Your propositions have been tested without success.

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

<br />
Town            TotalNrOfEmployees     AverageEmployeesPerTown    AverageEmployeesPerTownLessThen20<br />
Texas	        53	               8	                  13<br />
Maryland	49	               8	                  12

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

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

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