CODEWITHSUNDEEP
postgresqlpostgresql-9.2
Detox
Detox

Reputation: 112

confused on percentage result in query

I have been tasked to provide monthly percentage of successful completions. I have looked at the examples in other questions, and they just do not make sense to me.

I run a query to show completions (see below) in a 30 day span. However, to divide the number of <10405> codes by the total count of all befuddles me.

Can someone steer me to a solution? Thanks. Here is the current query for total completions:

SELECT groups.description AS Group,notes.date_service, services.code AS Service,
    clients.client_id, clients.name_lastfirst_cs AS Client, staff.staff_name_cs AS Staff
from notes, services, clients, staff, groups, address
WHERE notes.zrud_service = services.zzud_service
AND notes.zrud_client = clients.zzud_client
AND notes.zrud_staff = staff.zzud_staff
AND notes.zrud_group = groups.zzud_group
AND services.code IN  '10401','10402','10403','10405')
-- 10401 - 403 = successful; 10405 unsuccessful
AND notes.date_service BETWEEN (now() - '30 days'::interval)::timestamp AND now()

Upvotes: 0

Views: 49

Answers (1)

roman
roman

Reputation: 117485

changed your query a bit - added alias and change joins to ANSI-style:

select
    g.description as Group,
    n.date_service, s.code as Service,
    c.client_id,
    c.name_lastfirst_cs as Client,
    st.staff_name_cs as Staff,
    100 * sum(case when s.code in ('10401','10402','10403') then 1 else 0) / count(*) as Succesfull_Percentage
from notes as n
    inner join services as s on n.zrud_service = s.zzud_service
    inner join clients as c on n.zrud_client = c.zzud_client
    inner join staff as st on n.zrud_staff = st.zzud_staff
    inner join groups as g on n.zrud_group = g.zzud_group
where
    s.code in ('10401','10402','10403','10405') and
    n.date_service between (now() - '30 days'::interval)::timestamp and now()

Upvotes: 1

Related Questions