Finding an average - 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 2000 Forums

Transact-SQL (2000)

Finding an average

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2007-04-09 : 10:50:20

I have a basic query that look like this:

select compid,industry, busethicscode from tcompanies
where industry='Apparel'
order by busethicscode

Which produces a result like this:

CompID Industry BusEthicsCode
107344 Apparel No
107520 Apparel No
112508 Apparel Yes
112102 Apparel No

I need to display in percentage format how many fields have a 'BusEthicsCode' value of 'Yes'

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-09 : 10:57:00

Finding Average ? Or do you mean percentage of YES over ALL ?

You want to group by CompID ?


select a.CompID, TotalYes * 100.0 / Total as [Percentage]
from
(
    select CompID, sum(case when BusEthicsCode = 'Yes' then 1 else 0 end) as TotalYes
    from   tcompanies 
    where  industry = 'Apparel'
    group by CompID
) a
inner join
(
    select CompID, count(*) as Total
    from   tcompanies 
    where  industry = 'Apparel'
    group by CompID
) b
on a.CompID = b.CompID

evanburen
Posting Yak Master

167 Posts

Posted - 2007-04-09 : 11:08:57

Thanks for the help.

Actually, I just need much less than this. In this example, I would just need to know the average of all companies where BusEthicsCode-'Yes' in this case BusEthicsCode = 75%

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-09 : 11:14:09

[code]select a.CompID, a.Industry, (TotalYes * 100.0 / (Select count(*) from tcompanies)) as [Percentage]
from
(
select CompID, Industry, sum(case when BusEthicsCode = 'Yes' then 1 else 0 end) as TotalYes
from tcompanies
group by CompID, Industry
) a[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

evanburen
Posting Yak Master

167 Posts

Posted - 2007-04-09 : 11:32:29

I'm sorry that I'm not making my question clear. I just need the percentage overall that = 'Yes'. I don't need group by compID. In my original example, there are 4 companies with an industry='Apparel' and 3 of the 4 have a BusEthicsCode = 'Yes' so the only final result I need is 75%.

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-09 : 11:45:17

[code]declare @t table
(
CompID int,
Industry varchar(10),
BusEthicsCode varchar(10)
)

insert @t
select 107344, 'Apparel', 'No' union all
select 107520, 'Apparel', 'No' union all
select 112508, 'Apparel', 'Yes' union all
select 112102, 'Apparel', 'No'

select (select sum(case when BusEthicsCode = 'Yes' then 1 else 0 end) from @t) *100.00/ (Select count(*) from @t) as [Percentage][/code]

"3 of the 4 have a BusEthicsCode = 'Yes' so the only final result I need is 75%"

But I can see only one row with BusEthicsCode = 'Yes'...or am I seeing things??

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

evanburen
Posting Yak Master

167 Posts

Posted - 2007-04-09 : 11:54:44

"3 of the 4 have a BusEthicsCode = 'Yes' so the only final result I need is 75%"

No, my thinking is just as sharp as smooth stone today. Thanks very much for your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-09 : 18:19:28

[code]SELECT Industry,
100.0 * SUM(CASE when BusEthicsCode = 'yes' then 1 else 0 end) / COUNT(DISTINCT CompID) AS [Yes],
100.0 * SUM(CASE when BusEthicsCode = 'no' then 1 else 0 end) / COUNT(DISTINCT CompID) AS [No]
FROM @t
GROUP BY Industry
ORDER BY Industry[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources