CODEWITHSUNDEEP
sqlpostgresql
Di Zou
Di Zou

Reputation: 4609

How do I get the total number of results returned from a subquery in Postgresql?

I am trying to get the total number of results returned from a subquery. This is my query:

select 
    count(r.reason_id) as num,
    cast (count(r.reason_id) as float) / (select count(*) as total from r) * 100.0 as pct
from (
    select
        case
            when rarreason != 0 then rarreason
            else rejectreason end as reason_id
    from 
        workorderlines 
    where
        (rarreason != 0 or rejectreason != 0)
    ) as r

group by
    r.reason_id

However, when I try and execute this, I get this error:

ERROR:  relation "r" does not exist
LINE 3: ...on_id) as float) / (select count(*) as total from r) * 100.0...
                                                             ^

********** Error **********

ERROR: relation "r" does not exist
SQL state: 42P01
Character: 112

How do I do this? I am using Postgresql 9.1. Thanks!

Upvotes: 2

Views: 281

Answers (2)

user359040
user359040

Reputation:

Try:

select 
    count(r.reason_id) as num,
    cast (count(r.reason_id) as float) / max(r.count_all) * 100.0 as pct
from (
    select
        case
            when rarreason != 0 then rarreason
            else rejectreason end as reason_id,
        count(*) over () as count_all
    from 
        workorderlines 
    where
        (rarreason != 0 or rejectreason != 0)
    ) as r

group by
    r.reason_id

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

Didn't check your logic but you can rearrange it like this:

with r as (
    select
        case
            when rarreason != 0 then rarreason
            else rejectreason end as reason_id
    from 
        workorderlines 
    where
        (rarreason != 0 or rejectreason != 0)
)
select 
    count(r.reason_id) as num,
    cast (count(r.reason_id) as float) / (select count(*) as total from r) * 100.0 as pct
from r
group by r.reason_id

Upvotes: 1

Related Questions