Conditional SQL logic-CodePudding

I have a simple table of voting frequencies of registered voters

create table public.campaign_202206 (
    registrant_id INTEGER not null references votecal.voter_registration (registrant_id),
    voting_frequency smallint
);

I want to insert values into this table with the count of elections that the voter has participated in among the past four elections:

insert into campaign_202206 (
  select registrant_id, count(*)
  from votecal.voter_participation_history
  where election_date in ('2021-09-14', '2020-11-03', '2020-03-03', '2018-11-06')
  group by registrant_id
);

However, if the count is 1, then I want to look at the participation from five elections ago on '2018-06-05' and if there is no participation in that election, I want to store the voting_frequency as 0 instead of 1.

insert into campaign_202206 (
  select
    registrant_id,
    case
      when count(*) = 1 then --- what goes here?
      else count(*)
    end as voting_frequency
  from votecal.voter_participation_history
  where election_date in ('2021-09-14', '2020-11-03', '2020-03-03', '2018-11-06')
  group by registrant_id
);

What would go in this case-when-then to get the value for this special case?

CodePudding user response：

Use a correlated subquery as foloows:

insert into campaign_202206 (
  select
    registrant_id,
    case when count(*) = 1 then
            (
                select count(*)
                  from votecal.voter_participation_history sqvph
                 where sqvph.election_date = '2018-06-05'
                   and sqvph.registrant_id = vph.registrant_id
            )
        else count(*)
    end as voting_frequency
  from votecal.voter_participation_history vph
  where election_date in ('2021-09-14', '2020-11-03', '2020-03-03', '2018-11-06')
  group by registrant_id
);

The resultset providers in the query need aliases for this to work.

CodePudding user response：

User nested case:

insert into campaign_202206 (
 select
  registrant_id, 
     case
     when count(*) = 1 then 
           case
              when (select count(*) from voter_participation_history
                     where election_date in ('2018-06-05') and registrant_id 
                    = v1.registrant_id) > 0  
              then 1
              else 0
           end 
  else count(*)
  end as voting_frequency from voter_participation_history v1 where 
   election_date in ('2021-09-14', '2020-11-03', '2020-03-03', '2018-11-06') 
   group by v1.registrant_id);