Snippet 007

1SELECT2    CASE3        WHEN d.Calculated_Age < 20                  THEN '<20'4        WHEN d.Calculated_Age BETWEEN 20 AND 29   THEN '20-29'5        WHEN d.Calculated_Age BETWEEN 30 AND 39   THEN '30-39'6        WHEN d.Calculated_Age BETWEEN 40 AND 49   THEN '40-49'7        WHEN d.Calculated_Age BETWEEN 50 AND 59   THEN '50-59'8        WHEN d.Calculated_Age BETWEEN 60 AND 69   THEN '60-69'9        WHEN d.Calculated_Age BETWEEN 70 AND 79   THEN '70-79'10        ELSE '80+'11    END AS AgeGroup,12    i.Approved_Name,13    COUNT(DISTINCT s.PROCHI) AS PatientCount14FROM Project_12345..Prescribing_Scripts s15INNER JOIN Project_12345..Prescribing_Scripts_Prescribing_Item i16    ON s.hic_drugID = i.hic_drugID17INNER JOIN Project_12345..Demography_Current d18    ON s.PROCHI = d.PROCHI19GROUP BY20    CASE21        WHEN d.Calculated_Age < 20                  THEN '<20'22        WHEN d.Calculated_Age BETWEEN 20 AND 29   THEN '20-29'23        WHEN d.Calculated_Age BETWEEN 30 AND 39   THEN '30-39'24        WHEN d.Calculated_Age BETWEEN 40 AND 49   THEN '40-49'25        WHEN d.Calculated_Age BETWEEN 50 AND 59   THEN '50-59'26        WHEN d.Calculated_Age BETWEEN 60 AND 69   THEN '60-69'27        WHEN d.Calculated_Age BETWEEN 70 AND 79   THEN '70-79'28        ELSE '80+'29    END,30    i.Approved_Name31ORDER BY PatientCount DESC

1# Reusing df from the previous example2result = (df.groupby(["AgeGroup", "Approved_Name"])["PROCHI"]3            .nunique()4            .reset_index(name="PatientCount")5            .sort_values("PatientCount", ascending=False))6result

1# Reusing df from the previous example2result <- aggregate(3    PROCHI ~ AgeGroup + Approved_Name,4    data = df,5    FUN = function(x) length(unique(x))6)7names(result)[3] <- "PatientCount"8result <- result[order(-result$PatientCount), ]9head(result, 20)