MS-Access Query to PostgreSQL View

I am converting a microsoft access query into a postgresql view. The query has obvious components that I have found reasonable answers to. However, I am still stuck on getting the final result:

SELECT All_Claim_Data.Sec_ID,
    Sum(IIf([Type]="LODE",IIf([Status]="Active",1,0),0)) AS LD_Actv, 
    Sum(IIf([Type]="LODE",IIf([Loc_Date]>#8/31/2017#,IIf([Loc_Date]<#9/1/2018#,1,0),0),0)) AS LD_stkd_17_18, 
    Sum(IIf([Type]="LODE",IIf([Loc_Date]>#8/31/2016#,IIf([Loc_Date]<#9/1/2017#,1,0),0),0)) AS LD_stkd_16_17,
    Sum(IIf([Type]="LODE",IIf([Loc_Date]<#1/1/1910#,IIf(IsNull([Clsd_Date]),1,(IIf([Clsd_Date]>#1/1/1900#,1,0))),0),0)) AS Actv_1900s, 
    Sum(IIf([Type]="LODE",IIf([Loc_Date]<#1/1/1920#,IIf(IsNull([Clsd_Date]),1,(IIf([Clsd_Date]>#1/1/1910#,1,0))),0),0)) AS Actv_1910s,
  FROM All_Claim_Data.Sec_ID,
  GROUP BY All_Claim_Data.Sec_ID,
  HAVING (((Sum(IIf([casetype_txt]="LODE",1,0)))>0));

Realizing I need to use CASE SUM WHEN, here is what I have worked out so far:

    CREATE OR REPLACE VIEW hgeditor.vw_test AS
     SELECT All_Claim_Data.Sec_ID,
     SUM (CASE WHEN(Type='LODE' AND WHEN(Status='Active',1,0),0)) AS LD_Actv, 
     SUM (CASE WHEN(Type='LODE' AND WHEN(Loc_Date>'8/31/2017' AND Loc_Date<'9/1/2018',1,0),0),0)) AS LD_stkd_17_18,
     SUM (CASE WHEN(Type='LODE' AND WHEN(Loc_Date<'1/1/1910' AND (IsNull(Clsd_Date),1,(WHEN([Clsd_Date]>'1/1/1900',1,0))),0),0)) AS Actv_1900s
    FROM All_Claim_Data.Sec_ID,
    GROUP BY All_Claim_Data.Sec_ID,
    HAVING (((SUM(IIf(Type='LODE',1,0)))>0));

The goal is to count the number of instances in which the Sec_ID has the following:

• has (Type = LODE and Status = Active) = SUM integer
• has (Type = LODE and Loc_Date between 8/31/2017 and 9/1/2018) = SUM Integer

My primary issue is getting a SUM integer to populate in the new columns

Answer

Case expressions are the equivalent to the Access IIF() functions, but WHEN isn’t a function so it isn’t used by passing a set of parameters. Think of it as being a tiny where clause instead, it evaluates one or more predicates to determine what to do, and the action taken is established by what you specify after THEN

CREATE OR REPLACE VIEW hgeditor.vw_test AS
SELECT
    All_Claim_Data.Sec_ID
  , SUM( CASE
        WHEN TYPE = 'LODE' AND
            STATUS = 'Active' THEN 1
        ELSE 0
    END ) AS LD_Actv
  , SUM( CASE
        WHEN TYPE = 'LODE' AND
            Loc_Date > to_date('08/31/2017','mm/dd/yyyy') AND
            Loc_Date < to_date('09/1/2018','mm/dd/yyyy')  THEN 1
        ELSE 0
    END ) AS LD_stkd_17_18
  , SUM( CASE
        WHEN TYPE = 'LODE' AND
            Loc_Date < to_date('1/1/1910','mm/dd/yyyy')  AND
            [Clsd_Date] > to_date('1/1/1900','mm/dd/yyyy')  THEN 1
        ELSE 0
    END ) AS Actv_1900s
FROM All_Claim_Data.Sec_ID
GROUP BY
    All_Claim_Data.Sec_ID
HAVING COUNT( CASE
    WHEN Type = 'LODE' THEN 1
END ) > 0
;

By the way, you should NOT be relying on MM/DD/YYYY as dates in Postgres

nb: Aggregate functions ignore NULL, take this example:

+----------+
| id value |
+----------+
| 1  x     |
| 2  NULL  |
| 3  x     |
| 4  NULL  |
| 5  x     |
+----------+

select
       count(*)      c_all
     , count(value)  c_value
from t

+-------+----------+
| c_all |  c_value |
+-------+----------+
|     5 |        3 |
+-------+----------+


select
       sum(case when value IS NOT NULL then 1 else 0 end) sum_case
     , count(case when value IS NOT NULL then 1 end)      count_case
from t

+----------+-------------+
| sum_case |  count_case |
+----------+-------------+
|        3 |           3 |
+----------+-------------+