CODEWITHSUNDEEP
mysql
user3612445
user3612445

Reputation: 145

Get percent from one column

I want to get the % of hard workers for each company but I don't know how. For example:

Google -> 66%
Microsoft -> 33%

Based in this DB:

CREATE TABLE a_test
    (id int ,`business` varchar(25),`name` varchar(25) , `hard_worker`  int);
INSERT INTO a_test (id, business, name, hard_worker) 
 VALUES (1, 'Google', 'Tom Rudhot', '1'), 
        (2, 'Microsoft', 'Thomas Carpenter', '0'),
        (3, 'Google', 'Peter Butcher', '1'),
        (4, 'Microsoft', 'Sookie Page', '0'),
        (5, 'Microsoft', 'Roonie Redneck', '1'),
        (6, 'Google', 'Robbyn Stackhouse', '0');

http://www.sqlfiddle.com/#!9/33fed/1

Upvotes: 1

Views: 75

Answers (3)

dnoeth
dnoeth

Reputation: 60462

Try this (fiddle):

SELECT
   business,
   100 * count(*)
       / (select count(*) 
          FROM a_test
          where hard_worker = 1)
FROM a_test
where hard_worker = 1
group by business

Edit:

Previous query returns the percentage of the hard workers of a company vs. all hard workers.

But you seems to want hard vs. all workers per company. This is much easier:

SELECT
   business,
   100 * sum(case when hard_worker = 1 then 1 end) -- hard workers
       / count(*) -- all workers
FROM a_test as t1
group by business

Based on your 0/1 implementation you might simply use

   100 * sum(hard_worker)
       / count(*) -- all workers

Upvotes: 2

Sibster
Sibster

Reputation: 3189

simplest way would be (cant test it at the moment as sql fiddle seems down)

SELECT business,100 * (sum(hard_worker)/ count(*) )
FROM a_test group by business

Upvotes: 1

Venkatesh Panabaka
Venkatesh Panabaka

Reputation: 2154

I think these sql useful to you.

SELECT * FROM (
SELECT ROUND((Google_count/Google_total_count)* 100,2)  AS Google FROM (
SELECT COUNT(business) AS Google_total_count FROM a_test WHERE business = "Google") AS c,
(SELECT COUNT(business) AS Google_count FROM a_test WHERE hard_worker =1 AND business = "Google")AS d) ma
JOIN
(
SELECT ROUND((Microsoft_count/Microsoft_total_count)* 100,2)  AS microsoft FROM (
SELECT COUNT(business) AS Microsoft_total_count FROM a_test WHERE business = "Microsoft") AS a,

(SELECT COUNT(business) AS Microsoft_count FROM a_test WHERE hard_worker =1 AND business = "Microsoft") AS b) ma1

Thank you

Upvotes: 1

Related Questions