Compute aggregates by specifying a series of aggregate columns. Note that this function by default retains the grouping columns in its output. To not retain grouping columns, set spark.sql.retainGroupColumns to false.

The available aggregate methods are defined in org.apache.spark.sql.functions.

// Selects the age of the oldest employee and the aggregate expense for each department

// Scala:
import org.apache.spark.sql.functions._
df.groupBy("department").agg(max("age"), sum("expense"))

// Java:
import static org.apache.spark.sql.functions.*;
df.groupBy("department").agg(max("age"), sum("expense"));

Note that before Spark 1.4, the default behavior is to NOT retain grouping columns. To change to that behavior, set config variable spark.sql.retainGroupColumns to false.

// Scala, 1.3.x:
df.groupBy("department").agg($"department", max("age"), sum("expense"))

// Java, 1.3.x:
df.groupBy("department").agg(col("department"), max("age"), sum("expense"));

(Java-specific) Compute aggregates by specifying a map from column name to aggregate methods. The resulting DataFrame will also contain the grouping columns.

The available aggregate methods are avg, max, min, sum, count.

// Selects the age of the oldest employee and the aggregate expense for each department
import com.google.common.collect.ImmutableMap;
df.groupBy("department").agg(ImmutableMap.of("age", "max", "expense", "sum"));

(Scala-specific) Compute aggregates by specifying a map from column name to aggregate methods. The resulting DataFrame will also contain the grouping columns.

The available aggregate methods are avg, max, min, sum, count.

// Selects the age of the oldest employee and the aggregate expense for each department
df.groupBy("department").agg(Map(
  "age" -> "max",
  "expense" -> "sum"
))

(Scala-specific) Compute aggregates by specifying the column names and aggregate methods. The resulting DataFrame will also contain the grouping columns.

The available aggregate methods are avg, max, min, sum, count.

// Selects the age of the oldest employee and the aggregate expense for each department
df.groupBy("department").agg(
  "age" -> "max",
  "expense" -> "sum"
)

Returns a KeyValueGroupedDataset where the data is grouped by the grouping expressions of current RelationalGroupedDataset.

Compute the mean value for each numeric columns for each group. The resulting DataFrame will also contain the grouping columns. When specified columns are given, only compute the mean values for them.

Count the number of rows for each group. The resulting DataFrame will also contain the grouping columns.

Compute the max value for each numeric columns for each group. The resulting DataFrame will also contain the grouping columns. When specified columns are given, only compute the max values for them.

Compute the average value for each numeric columns for each group. This is an alias for avg. The resulting DataFrame will also contain the grouping columns. When specified columns are given, only compute the average values for them.

Compute the min value for each numeric column for each group. The resulting DataFrame will also contain the grouping columns. When specified columns are given, only compute the min values for them.

(Java-specific) Pivots a column of the current DataFrame and performs the specified aggregation. This is an overloaded version of the pivot method with pivotColumn of the String type.

Pivots a column of the current DataFrame and performs the specified aggregation. This is an overloaded version of the pivot method with pivotColumn of the String type.

// Compute the sum of earnings for each year by course with each course as a separate column
df.groupBy($"year").pivot($"course", Seq("dotNET", "Java")).sum($"earnings")

Pivots a column of the current DataFrame and performs the specified aggregation.

Spark will eagerly compute the distinct values in pivotColumn so it can determine the resulting schema of the transformation. To avoid any eager computations, provide an explicit list of values via pivot(pivotColumn: Column, values: Seq[Any]).

// Compute the sum of earnings for each year by course with each course as a separate column
df.groupBy($"year").pivot($"course").sum($"earnings");

(Java-specific) Pivots a column of the current DataFrame and performs the specified aggregation.

There are two versions of pivot function: one that requires the caller to specify the list of distinct values to pivot on, and one that does not. The latter is more concise but less efficient, because Spark needs to first compute the list of distinct values internally.

// Compute the sum of earnings for each year by course with each course as a separate column
df.groupBy("year").pivot("course", Arrays.<Object>asList("dotNET", "Java")).sum("earnings");

// Or without specifying column values (less efficient)
df.groupBy("year").pivot("course").sum("earnings");

Pivots a column of the current DataFrame and performs the specified aggregation. There are two versions of pivot function: one that requires the caller to specify the list of distinct values to pivot on, and one that does not. The latter is more concise but less efficient, because Spark needs to first compute the list of distinct values internally.

// Compute the sum of earnings for each year by course with each course as a separate column
df.groupBy("year").pivot("course", Seq("dotNET", "Java")).sum("earnings")

// Or without specifying column values (less efficient)
df.groupBy("year").pivot("course").sum("earnings")

From Spark 3.0.0, values can be literal columns, for instance, struct. For pivoting by multiple columns, use the struct function to combine the columns and values:

df.groupBy("year")
  .pivot("trainingCourse", Seq(struct(lit("java"), lit("Experts"))))
  .agg(sum($"earnings"))

Pivots a column of the current DataFrame and performs the specified aggregation.

Spark will eagerly compute the distinct values in pivotColumn so it can determine the resulting schema of the transformation. To avoid any eager computations, provide an explicit list of values via pivot(pivotColumn: String, values: Seq[Any]).

// Compute the sum of earnings for each year by course with each course as a separate column
df.groupBy("year").pivot("course").sum("earnings")

Compute the sum for each numeric columns for each group. The resulting DataFrame will also contain the grouping columns. When specified columns are given, only compute the sum for them.