title: Converting MS SQL to Snowflake SQL, How do I return a count of records using a subquery
body: <p>I am converting a query from MS SQL to Snowflake SQL and I would like to know how to turn a record count using a subquery (or Snowflake equivalent). In the code below, &quot;NUM_VISITS&quot; returns the number of encounters that occurred for each patient.</p>
  <p>If I hardcode a static number in place of &quot;NUM_VISITS&quot;, the query works without issue, so I am confident the issue is how I am calling the subquery.  When running the code as presented, I get the following error:</p>
  <p>SQL compilation error: syntax error line 15 at position 20 unexpected 'SELECT'. syntax error line 15 at position 32 unexpected '('.</p>
  <pre><code>SELECT
      
      'HB' as &quot;TXN_SOURCE&quot;,
      enc.PAT_ID,
      enc.PAT_ENC_CSN_ID,
      enc.contact_date,
      enc.PRIMARY_LOC_ID,
      enc.department_id,
      CASE
          WHEN enc.DEPARTMENT_ID = 1229296 AND txn.PROC_ID = 12255396 THEN 'YES'
          ELSE 'NO'
      END as &quot;KITCHEN_CLASS&quot;,
      &quot;NUM_VISITS&quot; = (SELECT COUNT(enc2.contact_date)
                      FROM HI_DB.STG_EPICCLARITY_PHS.PAT_ENC enc2
                      JOIN HI_DB.STG_EPICCLARITY_PHS.HSP_TRANSACTIONS txn2
                          ON enc2.PAT_ENC_CSN_ID = txn2.PAT_ENC_CSN_ID
                      WHERE enc.pat_id = enc2.pat_id
                              enc2.CONTACT_DATE BETWEEN $StartDate AND $EndDate
                              AND txn2.PROC_ID IN (12255387,12255388,12255395,12255396)
                              AND enc2.SERV_AREA_ID = 12288)
               ,
      txn.PROC_ID
      
  FROM HI_DB.STG_EPICCLARITY_PHS.PAT_ENC enc
  JOIN HI_DB.STG_EPICCLARITY_PHS.HSP_TRANSACTIONS txn
      ON enc.PAT_ENC_CSN_ID = txn.PAT_ENC_CSN_ID
  
  WHERE enc.CONTACT_DATE BETWEEN $StartDate AND $EndDate
  AND txn.PROC_ID IN (12255387,12255388,12255395,12255396)
  AND enc.SERV_AREA_ID = 12288;
  </code></pre>
  <p>After resolving the syntax issue that was pointed out, I was able to get the code to work by calling the subquery from a JOIN.  Below is the functioning code:</p>
  <pre><code>SELECT
      
      'HB' as &quot;TXN_SOURCE&quot;,
      enc.PAT_ID,
      enc.PAT_ENC_CSN_ID,
      enc.contact_date,
      enc.PRIMARY_LOC_ID,
      enc.department_id,
      CASE
          WHEN enc.DEPARTMENT_ID = 1229296 AND txn.PROC_ID = 12255396 THEN 'YES'
          ELSE 'NO'
      END as &quot;KITCHEN_CLASS&quot;,
      cnt.COUNT,
      txn.PROC_ID
      
  FROM HI_DB.STG_EPICCLARITY_PHS.PAT_ENC enc
  JOIN HI_DB.STG_EPICCLARITY_PHS.HSP_TRANSACTIONS txn
      ON enc.PAT_ENC_CSN_ID = txn.PAT_ENC_CSN_ID
  LEFT JOIN (SELECT   enc2.PAT_ID,
                      COUNT(enc2.contact_date) as &quot;COUNT&quot;
             FROM HI_DB.STG_EPICCLARITY_PHS.PAT_ENC enc2
             JOIN HI_DB.STG_EPICCLARITY_PHS.HSP_TRANSACTIONS txn2
                  ON enc2.PAT_ENC_CSN_ID = txn2.PAT_ENC_CSN_ID
             WHERE enc2.CONTACT_DATE BETWEEN $StartDate AND $EndDate
                  AND txn2.PROC_ID IN (12255387,12255388,12255395,12255396)
                  AND enc2.SERV_AREA_ID = 12288
            GROUP BY enc2.PAT_ID) as cnt ON enc.PAT_ID = cnt.PAT_ID
  
  WHERE enc.CONTACT_DATE BETWEEN $StartDate AND $EndDate
  AND txn.PROC_ID IN (12255387,12255388,12255395,12255396)
  AND enc.SERV_AREA_ID = 12288;
  </code></pre>