CODEWITHSUNDEEP
mysqlmysql-workbench
civam
civam

Reputation: 116

Use nested subquery to fetch value with multiple condition

I have a table named test_plan (id, unit, num) I inserted some values

INSERT INTO `test_plan` (`id`, `unit`, `num`) VALUES 
('1', '1', '12'),
('2', '1', '13'),
('3', '1', '14'),
('4', '1', '10'),
('5', '2', '10'),
('6', '2', '9'),
('7', '2', '-1'),
('8', '2', '-1'),
('9', '2', '-1'),
('10', '3', '-1'),
('11', '3', '-1'),
('12', '3', '-1');

I have to fetch unit what is fraction of each unit to total unit when num is not equals to -1 i.e.after run the query it display as unit 1 is 100% completed, unit 2 is 40% completed, unit 3 is 0% completed as row wise. I can count the number of each unit but not the how much it completed.

I tried JOIN for this

SELECT a.unit, numb / count(*) as frac FROM test_plan as a 
LEFT OUTER JOIN (SELECT unit, count(num) as numb FROM test_plan where num != -1 group by unit) as b 
ON a.unit = b.unit group by a.unit;

Upvotes: 0

Views: 80

Answers (1)

pala_
pala_

Reputation: 9010

try this:

select unit, 
    (sum(case when num = -1 then 0 else 1 end) / count(*)) * 100 as pct_complete
from lecture_plan group by unit;

there's no need for a nested sub query, the combination of aggregation and the case statement is sufficient

Upvotes: 1

Related Questions