CODEWITHSUNDEEP
sqlpostgresqlcaseanalytics
hansolo
hansolo

Reputation: 973

Assign Results from query to variable for use in case statement

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

Upvotes: 1

Views: 1467

Answers (2)

richyen
richyen

Reputation: 9968

You don't need values:

WITH
       myconstant1 as (select AVG(ptb_account_score_c) as val from salesforce_production.accounts),
       myconstant2 as (select 6 as val)
SELECT Id, ptb_account_score_c, myconstant1.val,
       myconstant2.val,
       case when ptb_account_score_c > myconstant1.val then 1 else 0 end as Blah
  FROM salesforce_production.accounts,myconstant1,myconstant2

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269593

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;

Upvotes: 1

Related Questions