12. Multiple aggregates, aliasing, and filtering groups

Real summaries compute several things at once and give them clean names.

orders.dropDuplicates().withColumn(
    "line_total", F.col("quantity") * F.col("unit_price")
).groupBy("category").agg(
    F.round(F.sum("line_total"), 2).alias("revenue"),
    F.count("*").alias("orders"),
    F.round(F.avg("unit_price"), 2).alias("avg_price"),
).orderBy(F.col("revenue").desc()).show()

A few things worth copying from that:

• Compute line_total before grouping — you can only aggregate columns that exist.
• Alias every aggregate. revenue, orders, avg_price are far nicer than sum(line_total).
• round(..., 2) keeps money readable.
• orderBy on an aliased aggregate sorts the summary.

Filtering groups — the HAVING pattern

To keep only some groups, filter after agg — on the aggregated column. In SQL this is HAVING; in PySpark it's just another filter:

orders.dropDuplicates().groupBy("category").agg(
    F.count("*").alias("orders")
).filter(F.col("orders") >= 6).show()

Mind the difference:

• filter before groupBy = which rows go into the groups (SQL WHERE).
• filter after agg = which groups survive (SQL HAVING).

Both are the same method — the position in the chain is what changes the meaning.

Your turn

For each category (deduplicated orders), compute revenue (sum(quantity * unit_price), rounded to 2) and keep only categories with revenue above 250. Return category and revenue, as result.

result = orders  # <- line_total, groupBy category, sum->revenue, keep revenue > 250
result.show()

result = (
    orders.dropDuplicates()
    .withColumn("line_total", F.col("quantity") * F.col("unit_price"))
    .groupBy("category")
    .agg(F.round(F.sum("line_total"), 2).alias("revenue"))
    .filter(F.col("revenue") > 250)
)