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

Sample data and desired results would help.

Gordon Linoff
– Gordon Linoff

2019-12-17 18:07:46 +00:00
Commented Dec 17, 2019 at 18:07 — Gordon Linoff
– Gordon Linoff, Commented Dec 17, 2019 at 18:07

Gordon Linoff · Accepted Answer · 2019-12-17 18:09:12Z

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;

answered Dec 17, 2019 at 18:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hansolo Over a year ago

the params CTE is what I was looking for. Guess I am a dummy and didnt realize the cross join would get me what I wanted. THANK YOU

richyen · Accepted Answer · 2019-12-17 18:14:17Z

1

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

edited Dec 17, 2019 at 18:14

answered Dec 17, 2019 at 18:08

richyen

10.3k4 gold badges18 silver badges36 bronze badges

3 Comments

user330315 Over a year ago

The second values is perfectly valid select 6 as var2 can indeed be written as values (6)

forpas Over a year ago

myconstant1.val and myconstant2.val are columns not tables. What are they doing in the FROM clause?

forpas Over a year ago

@a_horse_with_no_name my comment was about: FROM salesforce_production.accounts,myconstant1.val,myconstant2.val which was the code before it was edited.

Collectives™ on Stack Overflow

Assign Results from query to variable for use in case statement

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related