CODEWITHSUNDEEP
postgresqldivide
bumblebeez
bumblebeez

Reputation: 11

Divide two Select statements?

I'm new to sql and I'm trying to divide two select statements, but not getting the right answer. I'm getting 70, but the answer should be 9.20% What am I doing wrong here?

select count(clocked_in_at - starts_at)
from master_shift
where clocked_in_at is not null
and clocked_in_at <> starts_at
and date_part('minute', clocked_in_at - starts_at) > 0
/ 
(select count(id) from master_shift where clocked_in_at is not null)

starts_at                 clocked_in_at
"2015-10-15 18:00:00+00";"2015-10-15 18:15:00+00"
"2015-10-20 17:00:00+00";"2015-10-20 17:00:00+00"
"2015-10-21 20:30:00+00";"2015-10-21 20:30:00+00"
"2015-10-22 20:00:00+00";"2015-10-22 20:00:00+00"
"2015-10-23 17:00:00+00";"2015-10-23 17:00:00+00"
"2015-10-23 20:30:00+00";"2015-10-23 20:30:00+00"
"2015-10-24 18:30:00+00";"2015-10-24 18:30:00+00"
"2015-10-26 17:45:00+00";"2015-10-26 17:45:00+00"
"2015-10-26 20:30:00+00";"2015-10-26 20:30:00+00"
"2015-10-27 15:54:00+00";"2015-10-27 15:54:00+00"

Upvotes: 1

Views: 443

Answers (1)

Patrick
Patrick

Reputation: 32364

You probably want this:

SELECT 100 * sum(extract(second from (clocked_in_at - starts_at))) / count(id)
FROM master_shift
WHERE clocked_in_at IS NOT NULL
AND clocked_in_at <> starts_at
AND date_part('minute', clocked_in_at - starts_at) > 0;

Upvotes: 1

Related Questions