Assign Results from query to variable for use in case statement

Question

I am trying to take the avg of a total sample column and compare to the column value off each specific record.

I have done this in SQL Server by declaring a variable and then setting it to the results of a query.

I am trying to do the same thing in PG, but I am not having any success.

In the below sample, myconstant2 works because of the hard coded value but myconstant does not because the value is set to the single row query result.

Any pointers here?

with myconstant (var1) as 
(
    values (select AVG(ptb_account_score_c) 
            from salesforce_production.accounts)
),
myconstant2 (var2) as 
(
     values(6)
)
select
    Id,
    ptb_account_score_c,
    var1,
    var2,
    case 
       when ptb_account_score_c > var1 then 1 else 0 
    end as Blah
from
    salesforce_production.accounts, myconstant, myconstant2

Answer 1

I think you just want a window function:

select a.*,
       (case when ptb_account_score_c > avg(a.ptb_account_score_c) over () then 1 else 0 end) as Blah
from salesforce_production.accounts a;

If you wanted, you could combine these into a single CTE:

with params as (
    select AVG(ptb_account_score_c) as var1, 6 as var2
    from salesforce_production.accounts
   )
select a.id, a.ptb_account_score_c,
       params.var1, params.var2,
       (case when a.ptb_account_score_c > params.var1 then 1 else 0 end) as Blah
from salesforce_production.accounts a cross join
     params;