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

SQL Server 2005 Forums

Transact-SQL (2005)

Typical requirement

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-07-22 : 06:20:05

I have a typical requirement. My aim is to find out the race of the person by giving input as firstname, lastname and zipcode.

Tbl_Zipcode
Zip Blac Asian White
965 12.3 3.4 2.00
876 23.1 4.6 7.80

Tbl_LName
Lname pctBlac pctAsian pctwhite
Lee 40.0 2.00 1.10
Sam 2.46 1.90 3.21

Tbl_FName
Fname pctBlac pctAsian pctWhite
Bret 2.50 2.34 1.01
Oram 1.90 5.8 0.13

I have 3 different tables Tbl_Zipcode, Tbl_LName, Tbl_Fname. if i have data like 'Sam','Oram','965'.
If i query individually on each table i will get data like this

Select * from Tbl_Zipcode where Zip = 965
Select * from Tbl_Lname where Zip = 'Sam'
Select * from Tbl_Fname where Zip = 'Oram'

Zip Blac Asian White
965 12.3 3.4 2.00

Lname pctBlac pctAsian pctwhite
Sam 2.46 1.90 3.21

Fname pctBlac pctAsian pctWhite
Oram 1.90 5.8 0.13

Now i want to sum the values of corresponding columns. i.e Tbl_Lname.Blac + Tbl_Fname.Blac + Tbl_Zipcode.Blac
i.e Blac is (2.46 + 1.90 + 12.3 = 16.66). Similarly for other columns.
White( 3.21 + 0.13 + 7.80 = 11.14)
Asian (1.90 + 5.8 + 4.6 = 12.3)

Now among the three i need to get largest value Here i.e Blac = 16.66. I need this as Final out put.
So what ever the input i give it has to select rows form individaul tables and need to sum corresponding columns
and the final greatest value should be taken as output

G. Satish

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-22 : 08:44:40

try


select top 1 sum(balc)+sum(asian)+sum(white) as summation from
(
	Select zip,blac,asian,white from Tbl_Zipcode where Zip = 965
	union all
	Select lname,blac,asian,white from Tbl_Lname where Zip = 'Sam'
	union all
	Select fname,blac,asian,white from Tbl_Fname where Zip = 'Oram'
) as t
group by zip
order by summation desc

Madhivanan

Failing to plan is Planning to fail

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-07-22 : 10:16:29

Thank You. Its working fine..

quote:

Originally posted by madhivanan

try


select top 1 sum(balc)+sum(asian)+sum(white) as summation from
(
	Select zip,blac,asian,white from Tbl_Zipcode where Zip = 965
	union all
	Select lname,blac,asian,white from Tbl_Lname where Zip = 'Sam'
	union all
	Select fname,blac,asian,white from Tbl_Fname where Zip = 'Oram'
) as t
group by zip
order by summation desc

Madhivanan

Failing to plan is Planning to fail

G. Satish

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-23 : 02:24:54

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources