MYSQL Query Multiple Condition and SUM function

Question

Well, I have these columns in my table:

wo_number
crew_est
manhour_est
status ('FINISH' or NULL)
progress (this isn't in the table)

I need to do this calculation for progress:

progress = SUM(crew_est * manhour_est) WHERE status = 'FINISH') / (SUM(crew_est * manhour_est) WHERE status IS NULL);

I need to do the calculation above and group the result by wo_number. I've looking for the answer anywhere but still no luck and I've no idea where to start.

Any help will be much appreciated. Thank you.

So, the progress will be per wo_number?

Gurwinder Singh
– Gurwinder Singh

2016-12-31 07:57:21 +00:00
Commented Dec 31, 2016 at 7:57 — Gurwinder Singh
– Gurwinder Singh, Commented Dec 31, 2016 at 7:57
@GurwinderSingh yes, correct.

M Ansyori
– M Ansyori

2016-12-31 07:59:41 +00:00
Commented Dec 31, 2016 at 7:59 — M Ansyori
– M Ansyori, Commented Dec 31, 2016 at 7:59

Gurwinder Singh · Accepted Answer · 2017-02-12 04:13:25Z

2

You can use case for conditional aggregation:

select wo_Number,
    100.0 * SUM(case 
            when status = 'FINISH'
                then crew_est * manhour_est
            else 0
            end) / SUM(case 
            when status is null
                then crew_est * manhour_est
            else 0
            end) progress_percentage
from your_table
group by wo_number;

edited Feb 12, 2017 at 4:13

answered Dec 31, 2016 at 7:59

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

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

3 Comments

M Ansyori Over a year ago

I forgot to add, how do I retrieve the progress result in percentage? thanks in advance, your query is working.

Gurwinder Singh Over a year ago

@MAnsyori Is the percentage not just multiplied by 100?

M Ansyori Over a year ago

Correct, just multiplied the percentage by 100. Thank you so much.

Collectives™ on Stack Overflow

MYSQL Query Multiple Condition and SUM function

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related