Aggregate query results

Goal

Summarize records with HOQS aggregate functions, group them, filter the groups, and bucket by time period.

Prerequisites

You can already run a basic query — see Query and filter records.

Steps

1. Run a basic aggregate

Every aggregate in SELECT needs an alias:

SELECT COUNT(*) AS total FROM core.Contact LIMIT 1
SELECT SUM(amount) AS revenue, AVG(amount) AS avgDeal
FROM core.Opportunity
LIMIT 1

2. Choose the right COUNT variant

SELECT COUNT(*) AS total FROM core.Contact LIMIT 1
SELECT COUNT(email) AS withEmail FROM core.Contact LIMIT 1
SELECT COUNT(DISTINCT status) AS uniqueStatuses FROM core.Contact LIMIT 1

COUNT(*) counts all rows. COUNT(field) counts non-null values. COUNT(DISTINCT field) counts unique non-null values.

3. Group results with GROUP BY

Non-aggregate fields in SELECT must appear in GROUP BY:

SELECT status, COUNT(*) AS n
FROM core.Contact
GROUP BY status
ORDER BY n DESC
LIMIT 100

4. Filter groups with HAVING

WHERE filters individual rows before aggregation; HAVING filters groups after:

SELECT status, COUNT(*) AS n
FROM core.Contact
GROUP BY status
HAVING COUNT(*) > 10
ORDER BY n DESC
LIMIT 100

5. Group by a time bucket

Time functions extract a date component for grouping, and work on Timestamp and Date fields:

SELECT YEAR(closedDate) AS yr, QUARTER(closedDate) AS qtr, SUM(amount) AS revenue
FROM core.Opportunity
GROUP BY YEAR(closedDate), QUARTER(closedDate)
ORDER BY yr ASC, qtr ASC
LIMIT 100

Available functions: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE.

Verification

Confirm every non-aggregate field in your SELECT also appears in GROUP BY (otherwise the query is rejected with FIELD_NOT_IN_GROUP_BY), and that every aggregate expression carries an alias.