CODEWITHSUNDEEP
sqlsql-servert-sql
mohan111
mohan111

Reputation: 8865

how to get percentage based on counts

I have a sample table 

ID  Name    today_cnt   yesterday_cnt
1   mohan     10020        10080
1   mohan      1200        10080

how can I get the percentage values for count difference

1) 10080 - 10020 = 60 = 5 % >> small difference in record counts

2) 10080 - 1200 = 8800 = 90 % >> large difference in record counts

output : 

ID  Name    today_cnt   yesterday_cnt  Percentage
1   mohan    10020          10080         5%
1   mohan    1200           10080      80%

Upvotes: 0

Views: 73

Answers (4)

Ananda G
Ananda G

Reputation: 2539

The select query will be like this

SELECT s.id AS id, Name, 
case when yesterday_count > today_count
then cast(round ((yesterday_count - today_count) / yesterday_count * 100, 2) as decimal(3,0)) else 5 end as 'increment'
FROM users_students AS s

Check here in SQLFiddle

Upvotes: 0

sepupic
sepupic

Reputation: 8687

Not sure about your '5%', but smth like this

    declare @t table (ID int,  Name varchar(100),   today_cnt int,  yesterday_cnt int);
    insert into @t values
    (1,   'mohan',     10020,       10080),
    (1,   'mohan',      1200,       10080),
    (1,   'mohan',      10090,       10080)


    select *, 
           yesterday_cnt - today_cnt as diff,
           case when yesterday_cnt > today_cnt 
                then cast(round ((yesterday_cnt - today_cnt) *1. / yesterday_cnt * 100, 2) as decimal(3,1)) 
                else 0
           end as [%]
    from @t

Upvotes: 2

Manasa Chakka
Manasa Chakka

Reputation: 121

select *, (yesterday_cnt-today_cnt)/100 AS Percentage from tablename

Hope this is what you are looking for..

Upvotes: 0

Jeanne Chaudanson
Jeanne Chaudanson

Reputation: 131

SELECT ID, Name, today_cnt, yesterday_cnt, 
       ROUND((yesterday_cnt-today_cnt)/yesterday_cnt*100) AS Percentage
FROM TABLE;

Something like this ? I would not advise concatenating the '%' if you want to use the Percentage column as an input, though.

Upvotes: 1

Related Questions