December 4, 2008 at 3:25 pm
I have a table which has structure something like
ID ColA ColB ColC
1 1 1 0
2 0 1 1
3 0 0 1
4 1 1 1
5 1 1 0
Now there are 5 recordsi need to calculate percentage of each col ColA, ColB, ColC to see whats percentage of each col has value 1. ANy help in getting this sql will be greatly appreciated. I want to avoid using too many variables . any direct way of calculating this in 1 sql. TIA
December 4, 2008 at 3:35 pm
This works if your data is always 1 and 0.
Declare @table table(id int, colA int, colb int, colc int)
Insert into @table
Select
1 , 1 , 1 , 0
Union All
Select
2, 0 , 1 , 1
Union All
Select
3 , 0, 0 , 1
Union All
Select
4 , 1 , 1, 1
Union All
Select
5 , 1, 1 , 0
Select
Sum(cola)/Convert(Float, Count(id)),
Sum(colb)/Convert(Float, Count(id)),
Sum(Colc)/Convert(Float, Count(id))
From
@table
Will there ever be values other than 1 or 0? If so how do you know what value you are trying to find the percentage for?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 4, 2008 at 3:59 pm
Cool, that worked so well. Thanks Jack
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply