Aggregate functions

Count order lines and distinct products, then summarize line values.

Step 1 of 6 · Learn

Count lines and products

agg produces a one-row summary. count("*") counts every row; count(column) skips nulls and countDistinct counts distinct non-null values. There are nine order lines, but products can appear on more than one line. None of these expressions sums quantity.

Lesson reference: PySpark and Scala

Count lines and products

PySpark example

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

result = order_items.agg(count("*").alias("value_count"))
result.show()

Scala example

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

val result = order_items.agg(count("*").alias("value_count"))
result.show()

Sum line values

line_total is the value of every unit on one line, so sum(line_total) adds line values without multiplying by quantity again. The example includes every line. These are order values, not recognized revenue; the linked orders have different statuses.

PySpark example

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

result = order_items.agg(
    sum("line_total").alias("total_line_value")
)
result.show()

Scala example

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

val result = order_items.agg(
    sum("line_total").alias("total_line_value")
)
result.show()

Average line values

avg(line_total) computes an unweighted mean across lines, not an average unit price or order value. Round the final average to two decimals. Null values are skipped; all-null inputs yield null, not zero.

PySpark example

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

result = order_items.agg(
    round(avg("line_total"), 2).alias("average_line_value")
)
result.show()

Scala example

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

val result = order_items.agg(
    round(avg("line_total"), 2).alias("average_line_value")
)
result.show()

example.pyPySpark

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

2. Aggregate the selected linesCount, sum, or average only the rows passed into agg.

3. Inspect the summaryLine count, distinct products, line value, and unit quantity answer different questions.

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 lines and products

Result1 rows

value_count
9

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.