Click here to Skip to main content
15,884,836 members
Home / Discussions / Database
   

Database

 
GeneralRe: Assist to solve this problem? Pin
current199925-Jun-11 6:19
current199925-Jun-11 6:19 
GeneralRe: Assist to solve this problem? PinPopular
Mycroft Holmes25-Jun-11 13:06
professionalMycroft Holmes25-Jun-11 13:06 
GeneralRe: Assist to solve this problem? Pin
Peter_in_278026-Jun-11 14:50
professionalPeter_in_278026-Jun-11 14:50 
GeneralRe: Assist to solve this problem? Pin
Mycroft Holmes26-Jun-11 14:56
professionalMycroft Holmes26-Jun-11 14:56 
GeneralRe: Assist to solve this problem? Pin
David Skelly26-Jun-11 22:29
David Skelly26-Jun-11 22:29 
AnswerRe: Assist to solve this problem? Pin
Blue_Boy25-Jun-11 21:42
Blue_Boy25-Jun-11 21:42 
GeneralRe: Assist to solve this problem? [modified] Pin
current199917-Jul-11 21:48
current199917-Jul-11 21:48 
AnswerRe: Assist to solve this problem? Pin
Niladri_Biswas4-Jul-11 0:44
Niladri_Biswas4-Jul-11 0:44 
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

GeneralRe: Assist to solve this problem? Pin
current199918-Jul-11 0:00
current199918-Jul-11 0:00 
QuestionStored procedure from SSIS Pin
Groulien24-Jun-11 0:11
Groulien24-Jun-11 0:11 
AnswerRe: Stored procedure from SSIS Pin
R. Giskard Reventlov24-Jun-11 1:57
R. Giskard Reventlov24-Jun-11 1:57 
AnswerRe: Stored procedure from SSIS [modified] Pin
UNCRushFan24-Jun-11 2:03
UNCRushFan24-Jun-11 2:03 
GeneralRe: Stored procedure from SSIS Pin
Groulien27-Jun-11 3:28
Groulien27-Jun-11 3:28 
GeneralRe: Stored procedure from SSIS Pin
UNCRushFan27-Jun-11 5:06
UNCRushFan27-Jun-11 5:06 
QuestionShow Statement Pin
programmervb.netc++23-Jun-11 6:30
programmervb.netc++23-Jun-11 6:30 
AnswerRe: Show Statement Pin
Blue_Boy23-Jun-11 6:50
Blue_Boy23-Jun-11 6:50 
GeneralRe: Show Statement Pin
programmervb.netc++23-Jun-11 7:11
programmervb.netc++23-Jun-11 7:11 
AnswerRe: Show Statement Pin
Pete O'Hanlon23-Jun-11 7:26
mvePete O'Hanlon23-Jun-11 7:26 
GeneralRe: Show Statement Pin
programmervb.netc++23-Jun-11 7:31
programmervb.netc++23-Jun-11 7:31 
GeneralRe: Show Statement Pin
jschell23-Jun-11 8:35
jschell23-Jun-11 8:35 
QuestionRe: Show Statement Pin
Eddy Vluggen23-Jun-11 9:14
professionalEddy Vluggen23-Jun-11 9:14 
QuestionSQL Jobs Output Pin
Groulien23-Jun-11 4:05
Groulien23-Jun-11 4:05 
AnswerRe: SQL Jobs Output [modified] Pin
UNCRushFan23-Jun-11 7:30
UNCRushFan23-Jun-11 7:30 
GeneralRe: SQL Jobs Output Pin
Groulien23-Jun-11 22:56
Groulien23-Jun-11 22:56 
Questionis these two queries same? Pin
Dhyanga23-Jun-11 3:08
Dhyanga23-Jun-11 3:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.