> ## Documentation Index
> Fetch the complete documentation index at: https://docs.junction.com/llms.txt
> Use this file to discover all available pages before exploring further.

# GROUP BY clause

> Use the GROUP BY clause in Junction Sense queries for date truncation, date part extraction, table column, and source column grouping.

## Overview

You specify how Junction Sense should dissect the selected data through a Group By clause. The [Aggregate expressions](/sense/query-dsl/select-clause#aggregate-a-table-column)
you specify would then be applied to the resulting groups.

Your Group By clause can contain one or more of the following expressions:

* a [Date Truncate expression](#group-by-a-truncated-datetime)

  * A synchronous [Query](/sense/using-query-api) can have at most one [Date Truncate expression](#group-by-a-truncated-datetime).
  * A [Continuous Query](/sense/using-continuous-query) must have exactly one [Date Truncate expression](#group-by-a-truncated-datetime).

* a [Date Part expression](#group-by-a-date-or-time-component)

* a [Table Column expression](#group-by-a-table-column-expression)

* a [Source Column expression](#group-by-a-source-column-expression)

You can mix and match any expression types, and organize them in any order. [Group Key Column expression](/sense/query-dsl/select-clause#select-the-group-key-columns)
tracks your declaration order.

## Group by a truncated datetime

Group the input rows based on the specified column expression truncated to the specified granularity.
This is similar to the `DATE_TRUNC` function in SQL.

<AccordionGroup>
  <Accordion title="Input argument" icon="arrow-right-to-bracket" iconType="duotone" defaultOpen>
    | Argument    | Remarks                                                                                                                            |
    | ----------- | ---------------------------------------------------------------------------------------------------------------------------------- |
    | date\_trunc | The date or time period to truncate the input datetime to.                                                                         |
    | arg         | The input expression — only an [Index Column expression](/sense/query-dsl/column-expressions#index-column-expression) is accepted. |
  </Accordion>

  <Accordion title="Output column name" icon="line-columns" iconType="duotone" defaultOpen>
    `group_key.$OFFSET` if this is the N-th expression in the `group_by` clause.

    See also: [Select the group key columns](/sense/query-dsl/select-clause#select-the-group-key-columns)
  </Accordion>
</AccordionGroup>

<CodeGroup>
  ```python Python DSL theme={null}
  import vitalx.aggregation as va

  # Truncate to every 1 day.
  va.select(...).group_by(
      va.date_trunc(va.Sleep.index(), 1, "day"),
  )

  # Truncate to every 3 months.
  va.select(...).group_by(
      va.date_trunc(va.Sleep.index(), 3, "month"),
  )
  ```

  ```jsonc JSON DSL theme={null}
  # Truncate to every 1 day.
  {
      "group_by": [
          {
              "date_trunc": { "value": 1, "unit": "day" },
              "arg": { "index": "sleep" }
          }
      ]
  }

  # Truncate to every 3 months.
  {
      "group_by": [
          {
              "date_trunc": { "value": 3, "unit": "month" },
              "arg": { "index": "sleep" }
          }
      ]
  }
  ```
</CodeGroup>

## Group by a date or time component

Group the input rows based on a specific date or time component extracted from the specified column expression.
This is similar to the `DATE_PART` function in SQL.

<AccordionGroup>
  <Accordion title="Input argument" icon="arrow-right-to-bracket" iconType="duotone" defaultOpen>
    | Argument   | Remarks                                                                                                                            |
    | ---------- | ---------------------------------------------------------------------------------------------------------------------------------- |
    | date\_part | The date or time unit to extract.                                                                                                  |
    | arg        | The input expression — only an [Index Column expression](/sense/query-dsl/column-expressions#index-column-expression) is accepted. |
  </Accordion>

  <Accordion title="Output column name" icon="line-columns" iconType="duotone" defaultOpen>
    `group_key.$OFFSET` if this is the N-th expression in the `group_by` clause.

    See also: [Select the group key columns](/sense/query-dsl/select-clause#select-the-group-key-columns)
  </Accordion>
</AccordionGroup>

<CodeGroup>
  ```python Python DSL theme={null}
  import vitalx.aggregation as va

  # Extract the weekday
  va.select(...).group_by(
      va.date_part(va.Sleep.index(), "weekday"),
  )

  # Extract the day-of-month
  va.select(...).group_by(
      va.date_part(va.Sleep.index(), "day")
  )
  ```

  ```jsonc JSON DSL theme={null}
  # Extract the weekday
  {
      "group_by": [
          {
              "date_part": "weekday",
              "arg": { "index": "sleep" }
          }
      ]
  }

  # Extract the day-of-month
  {
      "group_by": [
          {
              "date_part": "day",
              "arg": { "index": "sleep" }
          }
      ]
  }
  ```
</CodeGroup>

## Group by a Table Column expression

Group the input rows based on a [Table Column expression](/sense/query-dsl/column-expressions#table-column-expression).
This is similar to the generic `GROUP BY` clause in SQL.

<AccordionGroup>
  <Accordion title="Input argument" icon="arrow-right-to-bracket" iconType="duotone" defaultOpen>
    Any [Table Column expressions](/sense/query-dsl/column-expressions#table-column-expression) — summary or timeseries.
  </Accordion>

  <Accordion title="Output column name" icon="line-columns" iconType="duotone" defaultOpen>
    `group_key.$OFFSET` if this is the N-th expression in the `group_by` clause.

    See also: [Select the group key columns](/sense/query-dsl/select-clause#select-the-group-key-columns)
  </Accordion>
</AccordionGroup>

<CodeGroup>
  ```python Python DSL theme={null}
  import vitalx.aggregation as va

  # Group by calendar date and Stand Hour type (idle vs stand)

  va.select(
      va.group_key("*"),
      va.Timeseries.col("stand_hour").field("value").sum()
  ).group_by(
      va.date_trunc(va.Timeseries.index(), 1, "day"),
      va.Timeseries.col("stand_hour").field("type")
  )
  ```

  ```jsonc JSON DSL theme={null}

  # Group by calendar date and Stand Hour type (idle vs stand)
  {
      "group_by": [
          {
              "date_trunc": {
                  "value": 1,
                  "unit": "day"
              },
              "arg": { "index": "timeseries" }
          },
          {
              "timeseries": "stand_hour",
              "field": "type"
          }
      ]
  }
  ```
</CodeGroup>

## Group by a Source Column expression

Group the input rows based on a [Source Column expression](/sense/query-dsl/column-expressions#source-column-expression).
This is similar to a generic `GROUP BY` clause in SQL.

<Note>
  If both the *Provider* and *Source Type* Source Columns are present in the [Group By clause](/sense/query-dsl/group-by-clause),
  the implicit [Data Prioritization](/sense/data-prioritization) behavior would be disabled.
</Note>

<AccordionGroup>
  <Accordion title="Input argument" icon="arrow-right-to-bracket" iconType="duotone" defaultOpen>
    Any [Source Column expressions](/sense/query-dsl/column-expressions#source-column-expression) valid in the query context.
  </Accordion>

  <Accordion title="Output column name" icon="line-columns" iconType="duotone" defaultOpen>
    `group_key.$OFFSET` if this is the N-th expression in the `group_by` clause.

    See also: [Select the group key columns](/sense/query-dsl/select-clause#select-the-group-key-columns)
  </Accordion>
</AccordionGroup>

<CodeGroup>
  ```python Python DSL theme={null}
  import vitalx.aggregation as va

  # Group by the source provider and source type
  va.select(...).group_by(
      va.date_trunc(va.Sleep.index(), 1, "week"),
      va.Source.col("source_provider"),
      va.Source.col("source_type")
  )

  ```

  ```jsonc JSON DSL theme={null}
  # Group by the source provider and source type
  {
      "group_by": [
          {
              "date_trunc": {
                  "value": 1,
                  "unit": "week"
              },
              "arg": { "index": "sleep" }
          },
          { "source": "source_provider" },
          { "source": "source_type" }
      ]
  }
  ```
</CodeGroup>