Grouping: groupBy() and agg()

Relationship	Cardinality	Join condition
students.cohort_id → cohorts.id	many students → one cohort	JOIN cohorts ON students.cohort_id = cohorts.id
enrollments.student_id → students.id	many enrollments → one student	JOIN students ON enrollments.student_id = students.id
enrollments.course_id → courses.id	many enrollments → one course	JOIN courses ON enrollments.course_id = courses.id
job_offers.student_id → students.id	many job offers → one student	JOIN students ON job_offers.student_id = students.id

The summary queries you wrote with GROUP BY translate to a two-step chain: groupBy() then agg().

SELECT country, COUNT(*) AS student_count
FROM students
GROUP BY country;

students.groupBy("country").agg(F.count("id").alias("student_count"))

The aggregate functions

SQL	PySpark
`COUNT(col)`	`F.count("col")`
`SUM(col)`	`F.sum("col")`
`AVG(col)`	`F.avg("col")`
`MIN(col)` / `MAX(col)`	`F.min("col")` / `F.max("col")`

Several aggregates in one pass:

job_offers.groupBy("city").agg(
    F.count("id").alias("offers"),
    F.avg("salary_usd").alias("avg_salary"),
    F.max("salary_usd").alias("top_salary"),
)

Like SQL, aggregates skip NULLs — F.avg("score") averages only completed enrollments.

HAVING is just another filter

Here PySpark is actually simpler than SQL. There is no special keyword: after agg() you have a normal DataFrame, so you filter it:

SELECT category, COUNT(*) AS course_count
FROM courses
GROUP BY category
HAVING COUNT(*) > 2;

(courses
    .groupBy("category")
    .agg(F.count("id").alias("course_count"))
    .filter(col("course_count") > 2))

And the full SQL clause order you memorized — WHERE → GROUP BY → HAVING → ORDER BY → LIMIT — becomes one readable pipeline:

(job_offers
    .filter(col("remote") == False)                        # WHERE
    .groupBy("city")                                       # GROUP BY
    .agg(F.avg("salary_usd").alias("avg_salary"),
         F.count("id").alias("n"))
    .filter(col("n") >= 2)                                 # HAVING
    .orderBy(col("avg_salary").desc())                     # ORDER BY
    .limit(5))                                             # LIMIT

That pipeline shape is 80% of the PySpark you will write in Databricks, Glue, or Fabric. Congratulations — you have mirrored the whole Basics of selection I section in PySpark.

The aggregate functions

HAVING is just another filter

Practice