Snippet 007

SELECT
    CASE
        WHEN d.Calculated_Age < 20                  THEN '<20'
        WHEN d.Calculated_Age BETWEEN 20 AND 29   THEN '20-29'
        WHEN d.Calculated_Age BETWEEN 30 AND 39   THEN '30-39'
        WHEN d.Calculated_Age BETWEEN 40 AND 49   THEN '40-49'
        WHEN d.Calculated_Age BETWEEN 50 AND 59   THEN '50-59'
        WHEN d.Calculated_Age BETWEEN 60 AND 69   THEN '60-69'
        WHEN d.Calculated_Age BETWEEN 70 AND 79   THEN '70-79'
        ELSE '80+'
    END AS AgeGroup,
    i.Approved_Name,
    COUNT(DISTINCT s.PROCHI) AS PatientCount
FROM Project_12345..Prescribing_Scripts s
INNER JOIN Project_12345..Prescribing_Scripts_Prescribing_Item i
    ON s.hic_drugID = i.hic_drugID
INNER JOIN Project_12345..Demography_Current d
    ON s.PROCHI = d.PROCHI
GROUP BY
    CASE
        WHEN d.Calculated_Age < 20                  THEN '<20'
        WHEN d.Calculated_Age BETWEEN 20 AND 29   THEN '20-29'
        WHEN d.Calculated_Age BETWEEN 30 AND 39   THEN '30-39'
        WHEN d.Calculated_Age BETWEEN 40 AND 49   THEN '40-49'
        WHEN d.Calculated_Age BETWEEN 50 AND 59   THEN '50-59'
        WHEN d.Calculated_Age BETWEEN 60 AND 69   THEN '60-69'
        WHEN d.Calculated_Age BETWEEN 70 AND 79   THEN '70-79'
        ELSE '80+'
    END,
    i.Approved_Name
ORDER BY PatientCount DESC

# Reusing df from the previous example
result = (df.groupby(["AgeGroup", "Approved_Name"])["PROCHI"]
            .nunique()
            .reset_index(name="PatientCount")
            .sort_values("PatientCount", ascending=False))
result

# Reusing df from the previous example
result <- aggregate(
    PROCHI ~ AgeGroup + Approved_Name,
    data = df,
    FUN = function(x) length(unique(x))
)
names(result)[3] <- "PatientCount"
result <- result[order(-result$PatientCount), ]
head(result, 20)