CODEWITHSUNDEEP
sqlsql-serversql-server-2008
SaNa3819
SaNa3819

Reputation: 347

Calculation in sql

There is a table

  Name    Sales1    Sales2

  A         10       20

  b         30      40

want a table

Sale    A     B

Sales1   33%   66%

Sales2   42%   57%

The calculation is like for Sales1 --> A it will be 10/(10+20)*100

Sales2--->b it will be 20/(10+20)*100

sames goes for sales2

How can it will be done?

Upvotes: 0

Views: 55

Answers (2)

Milen
Milen

Reputation: 8867

Select a.name, cast(a.Sales1 as decimal(9,2))/cast(total as decimal(9,2))* 100 as  Percentage1, cast(a.Sales2 as decimal(9,2))/cast(total as decimal(9,2))* 100 as Percentage2
from 
(
    select *, (Sales1 + Sales2) as total
    from yourtable
) a

Edit: added extra casting for division :) working fiddle targeting sql server 2008: Here

Edit updated answer with basic case logic:

Select case when a.name = 'A' then 'Sales1' else 'Sales2' end as Sale, cast(a.Sales1 as decimal(9,2))/cast(total as decimal(9,2))* 100 as  Percentage1, cast(a.Sales2 as decimal(9,2))/cast(total as decimal(9,2))* 100 as Percentage2
from 
(
   select *, (Sales1 + Sales2) as total
   from yourtable
) a

Upvotes: 1

Danyal Sandeelo
Danyal Sandeelo

Reputation: 12391

Though you should have tried but here you go..TESTED!

 select sale, (a/(a+b))*100, (b/(a+b))*100 from `table`

here is the schema I tested on 

CREATE TABLE `table` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `sale` varchar(50) NOT NULL DEFAULT '0',
   `a` int(11) NOT NULL DEFAULT '0',  
   `b` int(11) NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

Upvotes: 0

Related Questions