Count Instances in Result Set

Question

I'm doing a query to monitor over usage of emergency medical services in a given time frame, in this case a quarter of a year. When I try to do a subquery that calculates the VisitCount, it pulls every claim recorded for that customer, up to 4000 in some cases. All I want are the number of claims generated from emergency services within the past 91 days, usually no more than 10.

Here are the relevant parts of the query with what I'm unsure of commented out:

SELECT
    UMI,
    PIN,
    Agreement,
    --sq.VisitCount,
    med.Code,
    Date
    
FROM main
/*
    INNER JOIN (
    SELECT Agreement, COUNT(*) as VisitCount
    FROM main
    GROUP BY Agreement
    ) AS sq ON main.Agreement = sq.Agreement
*/
--Inner Joins that work are excluded
    
WHERE 
    med.Code BETWEEN '99281' AND '99285'
    AND Date >= (CURRENT_DATE - 91)
    --AND VisitCount >= 3
--GROUP BY Agreement
ORDER BY VisitCount DESC
    ;

Answer 1

SELECT Agreement, COUNT(*) as VisitCount
FROM main
GROUP BY Agreement

will get all the counts for each agreement in the table (since there are no WHERE conditions). To limit by date in the counts, you need that condition in the subquery:

SELECT Agreement, COUNT(*) as VisitCount
FROM main
WHERE Date >= (CURRENT_DATE - 91)
GROUP BY Agreement

To avoid duplicating the condition (having it in your inner and outer query), you could use a CTE (most modern DBMSes support this):

WITH mainbydate AS
(
 SELECT *
 FROM main
 WHERE Date >= (CURRENT_DATE - 91)
)
SELECT *
FROM mainbydate INNER JOIN (
  SELECT Agreement, COUNT(*) as VisitCount
  FROM mainbydate
  GROUP BY agreement
 ) sq ON mainbydate.agreement = sq.agreement
WHERE med.Code BETWEEN '99281' AND '99285'