Group by

Summarize order lines by product, order, or both keys.

Step 1 of 6 · Learn

Count per group

groupBy defines groups by key; agg returns one row per group. The example counts lines for each product. Desk lamp appears on three lines, but those lines contain six units: a line count is not a quantity sum. orderBy makes the output deterministic.

Lesson reference: PySpark and Scala

Count per group

PySpark example

from pyspark.sql.functions import col, count, countDistinct, sum, avg, round

result = order_items.groupBy("product_id").agg(
    count("*").alias("line_count")
).orderBy("product_id")
result.show()

Scala example

import org.apache.spark.sql.functions._

val result = order_items.groupBy("product_id").agg(
    count("*").alias("line_count")
).orderBy("product_id")
result.show()

Combine metrics

One agg call can count lines and sum their values. Each metric describes the same group. Here products are the groups; switching to order_id reconstructs each order total from its component lines, without counting a whole-order total multiple times.

PySpark example

from pyspark.sql.functions import col, count, countDistinct, sum, avg, round

result = order_items.groupBy("product_id").agg(
    count("*").alias("line_count"),
    sum("line_total").alias("total_line_value")
).orderBy("product_id")
result.show()

Scala example

import org.apache.spark.sql.functions._

val result = order_items.groupBy("product_id").agg(
    count("*").alias("line_count"),
    sum("line_total").alias("total_line_value")
).orderBy("product_id")
result.show()

Group on two keys

Each group is a product/order combination. A product bought in several orders appears in several groups. This dataset has one line per combination, so the values stay equal to line totals; another dataset may have several lines to combine. No product subtotal is added automatically.

PySpark example

from pyspark.sql.functions import col, count, countDistinct, sum, avg, round

result = order_items.groupBy("product_id", "order_id").agg(
    sum("line_total").alias("total_line_value")
).orderBy(col("product_id").asc(), col("order_id").asc())
result.show()

Scala example

import org.apache.spark.sql.functions._

val result = order_items.groupBy("product_id", "order_id").agg(
    sum("line_total").alias("total_line_value")
).orderBy(col("product_id").asc(), col("order_id").asc())
result.show()

example.pyPySpark

from pyspark.sql.functions import col, count, countDistinct, sum, avg, round result = order_items.groupBy("product_id").agg( count("*").alias("line_count") ).orderBy("product_id") result.show()

1. Read order_itemsOne row per order line; quantity can exceed one.

2. Group by the displayed keysEach key combination produces one summary row.

3. Inspect the summaryCheck the output grain before comparing counts or totals.

Source order_items9 rows

order_item_id	order_id	product_id	quantity	line_total
1	1001	201	1	59.5
2	1001	202	1	30
3	1002	203	2	99
4	1002	206	1	50
5	1003	204	1	35
6	1004	203	3	149.97
7	1004	205	1	70.02
8	1005	203	1	49.99
9	1006	201	2	120

Count per group

Result6 rows

product_id	line_count
201	2
202	1
203	3
204	1
205	1
206	1

Compare the source columns with the transformed result.

The example is loaded in the editor. Run it as written, then try a small change.

Runs on the Spark backend. First startup may take a moment.

Run your code to see the result.