HI520 Unit 7 Aggregation and Grouping Query Example

Every Figure Says What It Was Computed Over: A Unit 7 Aggregation and Grouping Query Set Summarizing a Pediatric Asthma Program by Site and Quarter

[Student Name]

Purdue University Global

HI520: Database Design and SQL

Unit 7 Aggregation and Grouping Query

[Instructor Name]

[Date]

Calder Bay Pediatrics and every row and count are synthetic; the sources cited are real.

Parameters

To keep the statements readable and rerunnable, dates are passed as named parameters rather than typed into each query: :half_start and :half_end bound January 1 to July 1, 2026; :q_start and :q_end bound the second quarter, April 1 to July 1; and :year_start and :year_end bound the twelve months beginning July 1, 2025. Each upper bound is exclusive, so a period ends just before midnight on its last day.

Statement 1: Encounters by Site and Quarter

SELECT s.site_name, date_trunc('quarter', e.start_time) AS quarter_start, COUNT(*) AS encounters FROM encounter e JOIN site s ON s.site_id = e.site_id WHERE e.start_time >= :half_start AND e.start_time < :half_end GROUP BY s.site_name, quarter_start ORDER BY s.site_name, quarter_start;

1. Harbor Street: first quarter 612, second quarter 571.

2. Lakeview: 455 and 418.

3. Pine Hollow: 301 and 273.

4. Westfield: 498 and 462.

One row represents one site in one quarter. Each figure counts encounters, not children; a child seen four times counts four times.

Statement 2: Children Rather Than Visits

SELECT s.site_name, COUNT(DISTINCT e.patient_id) AS children FROM encounter e JOIN site s ON s.site_id = e.site_id WHERE e.start_time >= :q_start AND e.start_time < :q_end GROUP BY s.site_name ORDER BY s.site_name;

1. Harbor Street 402, Lakeview 298, Pine Hollow 205, Westfield 341.

One row represents one site in the second quarter. Each figure counts distinct children seen at that site. The four figures sum to 1,246, but only 1,198 distinct children were seen in the quarter, because 48 children visited two sites and are counted at each. The gap between statement 1's 1,724 encounters and these figures is explained by repeat visits, about 1.4 per child. A count of visits and a count of children answer different questions, and a table that does not say which one it holds will be read as the other.

Statement 3: A Rate Built in Two Stages

The quality team wants the share of children with persistent asthma whose most recent control score in the past year falls at or under 19 on the childhood instrument, the cutoff for asthma that is not well controlled (Liu et al., 2007). The first stage builds one row per eligible child with that child's latest score; the second groups by site. SELECT site_name, COUNT(*) AS children_with_score, SUM(CASE WHEN latest_score <= 19 THEN 1 ELSE 0 END) AS not_well_controlled, ROUND(100.0 * SUM(CASE WHEN latest_score <= 19 THEN 1 ELSE 0 END) / COUNT(*), 1) AS percent_not_well_controlled FROM (SELECT DISTINCT ON (e.patient_id) e.patient_id, s.site_name, e.control_score AS latest_score FROM encounter e JOIN site s ON s.site_id = e.site_id WHERE e.patient_id IN (SELECT patient_id FROM persistent_asthma_cohort) AND e.control_score IS NOT NULL AND e.start_time >= :year_start AND e.start_time < :year_end ORDER BY e.patient_id, e.start_time DESC) latest GROUP BY site_name ORDER BY site_name;

1. Harbor Street: 38 of 131, 29.0 percent.

2. Lakeview: 29 of 87, 33.3 percent.

3. Pine Hollow: 22 of 48, 45.8 percent.

4. Westfield: 31 of 104, 29.8 percent.

One row represents one site, assigned by the child's most recent scored encounter. The denominator is children in the persistent asthma cohort with at least one score in the year, 370 of the cohort's 412. The 42 children with no score in the year are excluded from every rate and reported beside the table: 14 at Harbor Street, 10 at Lakeview, 7 at Pine Hollow and 11 at Westfield.

Statement 4: Averages Only Where the Group Is Large Enough

SELECT s.site_name, ROUND(AVG(e.control_score), 1) AS mean_score, COUNT(*) AS scored_encounters FROM encounter e JOIN site s ON s.site_id = e.site_id WHERE e.control_score IS NOT NULL AND e.start_time >= :q_start AND e.start_time < :q_end GROUP BY s.site_name HAVING COUNT(*) >= 30 ORDER BY mean_score;

1. Pine Hollow 18.7, Lakeview 19.9, Westfield 20.4, Harbor Street 20.6.

One row represents one site in the second quarter. Each mean is computed over scored encounters only; AVG ignores nulls, so unscored visits do not pull the mean toward zero, but they are also invisible in it. HAVING removes any site with fewer than 30 scored encounters, because a mean from a handful of visits moves too much from one quarter to the next to compare.

Statement 5: Suppressing Small Groups Before Sharing

The table of children by site and primary language will go to a community advisory board. Groups under 11 children are withheld so that no child can be identified by combining a small count with what board members already know, a threshold widely used in public health data releases. SELECT s.site_name, COALESCE(p.primary_language, 'Not recorded') AS language, COUNT(DISTINCT p.patient_id) AS children FROM patient p JOIN encounter e ON e.patient_id = p.patient_id JOIN site s ON s.site_id = e.site_id WHERE e.start_time >= :q_start AND e.start_time < :q_end GROUP BY s.site_name, language HAVING COUNT(DISTINCT p.patient_id) >= 11 ORDER BY s.site_name, children DESC; Of 19 site and language groups, 3 were withheld, including Vietnamese-speaking children at Pine Hollow, who number 7. The shared table states that three groups were suppressed, so readers know the rows do not sum to the full count. One row represents children of one language seen at one site in the second quarter.

Why the Denominator Lines Matter

Statistics texts for health information work insist that every rate travels with its numerator, denominator and period (Horton, 2017). The five statements show why. Statement 1 and statement 2 describe the same quarter with figures that differ by more than 40 percent. Statement 3's rate would look better if the 42 unscored children were counted as controlled, and data quality frameworks treat that kind of silent gap as a completeness problem to be reported, not filled in (Weiskopf & Weng, 2013). Pine Hollow's lower mean in statement 4 matches its higher share of poorly controlled children in statement 3, which is the kind of agreement between two measures that makes a finding worth acting on.

References

Horton, L. A. (2017). Calculating and reporting healthcare statistics (5th ed.). AHIMA Press.

Liu, A. H., Zeiger, R., Sorkness, C., Mahr, T., Ostrom, N., Burgess, S., Rosenzweig, J. C., & Manjunath, R. (2007). Development and cross-sectional validation of the Childhood Asthma Control Test. Journal of Allergy and Clinical Immunology, 119(4), 817-825. https://doi.org/10.1016/j.jaci.2006.12.662

Weiskopf, N. G., & Weng, C. (2013). Methods and dimensions of electronic health record data quality assessment: Enabling reuse for clinical research. Journal of the American Medical Informatics Association, 20(1), 144-151. https://doi.org/10.1136/amiajnl-2011-000681