Use nested subquery to fetch value with multiple condition

Question

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;

What did you tried? What is the specific problem with your query? Showing some effort and writing good questions are highly improves your chance to get good answers. Please read How to Ask in help center to know more about the guidelines of asking in StackOverflow. — Pred
– Pred, Commented Mar 30, 2015 at 7:01
Essentially, you appear to want the SUM of one thing divided by the COUNT of another thing. Note that numbers do not normally need to be quoted. — Strawberry
– Strawberry, Commented Mar 30, 2015 at 7:13

pala_ · Accepted Answer · 2015-03-30 07:12:59Z

1

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

answered Mar 30, 2015 at 7:12

pala_

9,0101 gold badge18 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pala_ Over a year ago

you've got to show someone a fish before you can teach them how to fish

Collectives™ on Stack Overflow

Use nested subquery to fetch value with multiple condition

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related