CODEWITHSUNDEEP
sqlsql-server
bintelligent
bintelligent

Reputation: 23

Calculate percentage of a value in a column when count is given

I have a table with values below

id     name      approved      count
3      az        1             10
3      az        0             5
2      az        1             10
2      az        0             5
3      bz        1             10
3      bz        0             5
2      bz        1             10
2      bz        0             5

I need to calculate the % of approved =1 with my final view that shows

id     name      Rate     
3      az        66.66            
3      bz        66.66            
2      az        66.66          
2      bz        66.66

Where rate is (10/15)*100 i.e (count of approved = 1/sum of count for that id,name combination)*100

Upvotes: 1

Views: 106

Answers (3)

Pரதீப்
Pரதீப்

Reputation: 93694

Here is one way using conditional aggregation 

SELECT id,
       NAME,
       Isnull(Sum(CASE WHEN approved = 1 THEN [count] END) / Sum([count] * 1.0) * 100,0)
FROM  your_table
GROUP BY id, NAME

* 1.0 is used to avoid integer division

Upvotes: 2

Nolan Shang
Nolan Shang

Reputation: 2328

If you also want to return the detail of each line.

    create table #t(id int,name varchar(10),approved bit,count int)
    insert into #t
    select 3,'az',1,10 union all
    select 3,'az',0,5 union all
    select 2,'az',1,10 union all
    select 2,'az',0,5 union all
    select 3,'bz',1,10 union all
    select 3,'bz',0,5 union all
    select 2,'bz',1,10 union all
    select 2,'bz',0,5 
    select * from (
       select *,sum(case when approved=1 then count else 0 end )over()*1.0/sum(count)over() as perc from #t
    ) as t where t.approved=1 order by id desc

+----+------+----------+-------+----------------+
| id | name | approved | count | perc           |
+----+------+----------+-------+----------------+
| 3  | az   | 1        | 10    | 0.666666666666 |
| 3  | bz   | 1        | 10    | 0.666666666666 |
| 2  | bz   | 1        | 10    | 0.666666666666 |
| 2  | az   | 1        | 10    | 0.666666666666 |
+----+------+----------+-------+----------------+

Upvotes: 0

Javlon Ismatov
Javlon Ismatov

Reputation: 194

 select id,name,
 sum(case when approved=1 then round(cast(count as float)/15*100,2) end) 'Rate'
  from Your_Table
  group by id,name

Upvotes: 0

Related Questions