Filtration on columns created using CASE Postgresql-CodePudding

I have a table below:

id int
user_id int
created_at datetime
status varchar
type varchar

and I am trying to answer the question "Write a query that returns the user ID of all users that have created at least one ‘Refinance’ submission and at least one ‘InSchool’ submission."

I figured out how this will be done:

select
    a.user_id
from
    (select
        user_id,
        sum(
            case
                when type='Refinance' then 1 else 0 end) as "Refinancecount",
        sum(
            case
                when type='InSchool' then 1 else 0 end) as "Inschoolcount"
    from
        loans
    group by 1) as a
where a.Refinancecount>=1 and a.Inschoolcount>=1

When I run only the inner query, everything is fine. I am getting 3 columns but when I run the whole query it says column Refinancecount doesn't found. I looked on internet and found when I am aliasing my case columns then I should't wrap them in double quotes and the query worked fine using this suggestion. But I read more about creating columns using CASE in postgre, and found people do wrap column names in double quotes (I have attached an example screenshot). Then why my query doesn't work this way.

CodePudding user response：

Yea it's said column Refinancecount doesn't found because you need to use quote where a."Refinancecount">=1 and a."Inschoolcount">=1. If you name your column as "Inschoolcount" you need to refer to it with the "" otherwise a.Refinancecount will be converted to a.refinancecount

CodePudding user response：

You should quote those fields in the where clause. Try:

where "Refinancecount" >= 1 and "Inschoolcount" >= 1

By the way, you can write this query without inner query, by using having clause:

select
        user_id
from
        loans
group by 1
having sum(case when type='Refinance' then 1 else 0 end) >=1 and sum(case when type='InSchool' then 1 else 0 end) >= 1

CodePudding user response：

Why not make it simple like this

 select
    user_id
     from
    loans
   group by user_id having
   sum(
        case
            when type in ('Refinance', 
             'InSchool') then 1 else 0 end >=2)