CODEWITHSUNDEEP
sqlpostgresql
Leon Hooijer
Leon Hooijer

Reputation: 584

PostgreSQL group by higher then

I have a PostgreSQL (9.2) database from which I am trying to get some grouped data. The groups should be like: 1, 2, 3, 4, 5, >5 (higher then 5)

Is this possible? And if it is, how?

What I have so far:

select secondary_diagnoses_count 
from admissions 
group by secondary_diagnoses_count 
order by secondary_diagnoses_count;

Upvotes: 1

Views: 58

Answers (1)

roman
roman

Reputation: 117485

More of you data would be nice, but in general you can use case:

with cte as (
    select
        case
            when secondary_diagnoses_count > 5 then
                '>5'
            else
                secondary_diagnoses_count::text
        end as grp
    from admissions
)
select grp, count(*)
from cte 
group by grp
order by grp

PostgreSQL also allows to use aliased columns in group by so it could be written like this:

select
    case
        when secondary_diagnoses_count > 5 then
            '>5'
        else
            secondary_diagnoses_count::text
    end as grp
from admissions
group by grp
order by grp

sql fiddle demo

Upvotes: 2

Related Questions