How can i get sum through column wise

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)

How can i get sum through column wise

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-07-22 : 05:11:25

My table contains following data

Zip Blac White green
123 1.76 2.04 1.00
234 3.49 2.39 3.24

i want to get sum of all columns for given zipcode. Suppose if i give zipcode as '123' then i need to get result as sum of columns(blac,white,green) = 1.76 +2.04 +1.00 = 4.8

Output
4.8

How can i get this?

G. Satish

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-22 : 05:12:52

select Zip,(Blac +White +green) as summation from your_table
where zip=123

Madhivanan

Failing to plan is Planning to fail

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-07-22 : 05:31:28

Thank You. A slight change in my requirement. I want to know which column value is >2 for given zipcode.

i.e Get the columnname from the values Where value is > 2.
for Example in above given data for zip = 123 need to get columnname as result where value is > 2 otherwise display result as null if there is no value > 2.
Output
White

quote:
Originally posted by madhivanan

select Zip,(Blac +White +green) as summation from your_table
where zip=123

Madhivanan

Failing to plan is Planning to fail

G. Satish

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-07-22 : 05:39:37

Hi try this,



DECLARE @temp TABLE ( Zip INT, Blac DECIMAL(9,2), White DECIMAL(9,2), green DECIMAL(9,2) )
INSERT INTO @temp SELECT 123, 1.76, 2.04, 1.00
INSERT INTO @temp SELECT 234, 3.49, 2.39, 3.24

SELECT zip,SUM(val) as 'Sum'
FROM ( 
		SELECT zip,val FROM @temp 
		UNPIVOT ( val FOR COLUMNS IN ( blac,white,green) ) t 
	) P
WHERE p.val > 2
GROUP BY zip

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-07-22 : 05:56:55

Hi Nageswar, i want output as column Name. Not the sum of all columns.
If i give zip as "123" as input, then it needs to check in all columns. If any of the value in column is > 2 then that column name should appear as output.

For Example:

Zip Blac White green
123 1.76 2.04 1.00

In the above, If i took zip as "123", the value 2.04 is > 2(checking all values among columns). So it should return output as White. If i took zip as 234 then here all the values are > 2. Then it should give the column name of maximum value. i.e Blac

G. Satish

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-22 : 06:08:06

[code]
declare @tab table (Zip int, Blac decimal(18,2),White decimal(18,2),green decimal(18,2))
insert into @tab select 123, 1.76, 2.04, 1.00
insert into @tab select 234, 3.49, 2.39, 3.24

select zip,val,names into #temp
from @tab
unpivot (val for names in (blac,white,green))p

select t.names from #temp t
inner join (
select zip,max(val)as val
from #temp where zip =234 and val> 2 group by zip)s on s.val = t.val and s.zip = t.zip

drop table #temp
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources