skcah
Writing / Netsuite

SuiteQL GROUP BY Month, Quarter and Year

Group SuiteQL results by calendar month, quarter or year while keeping the output correctly sorted across multiple years.

2 minute read

For reliable chronological grouping, keep numeric year and period values in the result instead of sorting only by formatted labels.

This article concentrates on aggregation. For the underlying date-part syntax, return types and filtering patterns, see SuiteQL EXTRACT(YEAR FROM): year and month.

Group by month

SELECT
  EXTRACT(YEAR FROM transaction.trandate) AS report_year,
  EXTRACT(MONTH FROM transaction.trandate) AS report_month,
  COUNT(*) AS transaction_count,
  SUM(transaction.foreigntotal) AS total_amount
FROM transaction
WHERE transaction.trandate >= TO_DATE(?, 'YYYY-MM-DD')
  AND transaction.trandate <  TO_DATE(?, 'YYYY-MM-DD')
GROUP BY
  EXTRACT(YEAR FROM transaction.trandate),
  EXTRACT(MONTH FROM transaction.trandate)
ORDER BY
  report_year,
  report_month

Grouping by month without the year combines January 2025 with January 2026.

Add a readable month label

The label can be selected alongside the numeric sort fields:

SELECT
  EXTRACT(YEAR FROM transaction.trandate) AS report_year,
  EXTRACT(MONTH FROM transaction.trandate) AS report_month,
  TO_CHAR(transaction.trandate, 'YYYY-MM') AS month_label,
  COUNT(*) AS transaction_count
FROM transaction
GROUP BY
  EXTRACT(YEAR FROM transaction.trandate),
  EXTRACT(MONTH FROM transaction.trandate),
  TO_CHAR(transaction.trandate, 'YYYY-MM')
ORDER BY report_year, report_month

Every selected non-aggregate expression must also appear in GROUP BY.

Group by quarter

TO_CHAR(date, 'Q') returns the calendar quarter:

SELECT
  EXTRACT(YEAR FROM transaction.trandate) AS report_year,
  TO_CHAR(transaction.trandate, 'Q') AS report_quarter,
  COUNT(*) AS transaction_count
FROM transaction
GROUP BY
  EXTRACT(YEAR FROM transaction.trandate),
  TO_CHAR(transaction.trandate, 'Q')
ORDER BY report_year, report_quarter

This is a calendar quarter, not necessarily the organisation’s fiscal quarter.

Group by year

SELECT
  EXTRACT(YEAR FROM transaction.trandate) AS report_year,
  COUNT(*) AS transaction_count,
  SUM(transaction.foreigntotal) AS total_amount
FROM transaction
GROUP BY EXTRACT(YEAR FROM transaction.trandate)
ORDER BY report_year

Why months can be missing

Aggregation only returns periods that have matching rows. If April has no transactions, no April row is produced.

For a complete chart axis, either:

  • generate the required date series and outer join the totals; or
  • fill missing months in the application after the query.

The application approach is usually simplest for a short, fixed report. A generated series is useful when the database result itself must contain zero-value rows.