GROUP BY in SQL
Introduction
GROUP BY is the SQL clause that converts detailed rows into meaningful groups. Rows with the same grouping values are placed in the same group, after which aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX() calculate one result for each group.
Without grouping, an aggregate normally describes the entire filtered result set. With grouping, the same aggregate can answer questions for every department, customer, product, status, city, or calendar period. This makes GROUP BY central to dashboards, financial summaries, operational reports, reconciliation queries, quality checks, and interview problems.
SELECT department,
COUNT(*) AS employee_count
FROM employees
GROUP BY department;
Instead of returning one count for the whole employee table, this query returns one row per department. The most important design question is therefore not “which aggregate should I use?” but “what should one row in the final result represent?”
Why GROUP BY Is Needed
Suppose an employee table contains John and David in IT, Mary and Lisa in HR, and James in Finance. SELECT COUNT(*) FROM employees returns five because it treats all qualifying rows as one population. If the requirement is to count employees in each department, the department value must define separate populations.
| department | employee_count |
|---|---|
| IT | 2 |
| HR | 2 |
| Finance | 1 |
Conceptually, SQL reads the source rows, applies row filters, places rows into department groups, and calculates the count independently inside each group. A grouped query does not process one group at a time in the physical sense guaranteed by the language, but this mental model accurately explains the result.
Basic Syntax
SELECT grouping_expression,
aggregate_function(measure_expression)
FROM table_name
GROUP BY grouping_expression;
The grouping expression defines the categories, often called dimensions. The aggregate receives the values to measure. In a salary report, department is the dimension and salary is the measure.
SELECT department,
AVG(salary) AS average_salary
FROM employees
GROUP BY department;
Understanding Result Grain
Result grain describes what one output row represents. A query with no GROUP BY and only aggregates has one row for the complete filtered population. Grouping by department produces one row per department. Grouping by department and job title produces one row for each unique department and job-title combination.
Grain is the foundation of correct aggregation. If a sales report should contain one row per customer per month, both customer and month belong in the group key. Grouping only by customer merges all months, while grouping only by month merges different customers. Adding grouping columns generally creates more detailed groups; removing them produces higher-level summaries.
GROUP BY with COUNT()
COUNT(*) counts rows in each group, including rows whose individual columns contain NULL. It is appropriate for questions such as employees per department, orders per status, and events per day.
SELECT status,
COUNT(*) AS order_count
FROM orders
GROUP BY status;
COUNT(column) has different behavior because it counts only non-NULL values of that expression. A department containing ten employees but only eight email addresses produces ten with COUNT(*) and eight with COUNT(email). The choice must match the business question.
GROUP BY with SUM(), AVG(), MIN(), and MAX()
The other common aggregates summarize a measure within every group. SUM calculates a total, AVG calculates the arithmetic mean of non-NULL values, MIN returns the smallest non-NULL value, and MAX returns the largest. Several aggregates can be calculated together without scanning separate logical populations.
SELECT department,
COUNT(*) AS employee_count,
SUM(salary) AS payroll,
AVG(salary) AS average_salary,
MIN(salary) AS minimum_salary,
MAX(salary) AS maximum_salary
FROM employees
GROUP BY department;
| department | count | payroll | average | minimum | maximum |
|---|---|---|---|---|---|
| IT | 2 | 150000 | 75000 | 70000 | 80000 |
| HR | 2 | 125000 | 62500 | 60000 | 65000 |
| Finance | 1 | 75000 | 75000 | 75000 | 75000 |
These metrics are comparable because they use the same rows and group key. When reports combine metrics with different eligibility rules, conditional aggregation or separate prepared datasets make those differences explicit.
Grouping by Multiple Columns
Multiple grouping columns create one group for every unique combination. If employees are grouped by department and status, active IT employees form a different group from inactive IT employees, and both differ from active HR employees.
SELECT department,
status,
COUNT(*) AS employee_count
FROM employees
GROUP BY department, status
ORDER BY department, status;
Every additional grouping expression changes the grain. Do not add a column merely to satisfy an error about the select list without considering this effect. Adding employee name to a department summary could turn one row per department into one row per department and employee, destroying the intended aggregation.
WHERE Filters Rows Before Grouping
WHERE controls which source rows are eligible to enter any group. To count active employees by department, remove inactive employees first and then group the remaining rows.
SELECT department,
COUNT(*) AS active_employee_count
FROM employees
WHERE status = 'Active'
GROUP BY department;
This ordering affects both performance and meaning. A completed-revenue report should normally filter to completed orders in WHERE so cancelled and pending amounts never enter the customer groups.
HAVING Filters Groups After Aggregation
HAVING evaluates conditions against completed groups. It is used when the filtering decision depends on an aggregate, such as departments with at least five employees or customers whose completed purchases exceed 5,000.
SELECT customer_id,
SUM(total_amount) AS total_spent
FROM orders
WHERE status = 'Completed'
GROUP BY customer_id
HAVING SUM(total_amount) > 5000;
WHERE decides which orders participate. GROUP BY builds one group per customer. HAVING then removes customer groups whose sum does not pass the threshold. Moving the status condition into HAVING would blur row eligibility with group eligibility and may produce different or invalid logic.
WHERE vs HAVING
| Clause | Operates on | Typical condition |
|---|---|---|
WHERE | Individual source rows | status = 'Completed' |
HAVING | Aggregated groups | SUM(amount) > 5000 |
A non-aggregate condition that can be applied before grouping generally belongs in WHERE. Early filtering communicates intent and can reduce the rows that the database must join, group, and aggregate. HAVING remains essential for conditions that cannot be known until a group has been calculated.
Logical Query Processing Order
A useful simplified logical order is FROM and JOIN, WHERE, GROUP BY, aggregate calculation, HAVING, SELECT, DISTINCT, ORDER BY, and finally row limiting. This differs from the written order but explains why SELECT aliases are not portable in GROUP BY or HAVING and why joins can multiply rows before aggregation occurs.
FROM / JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT / FETCH
The optimizer may physically rearrange operations while preserving equivalent results. Logical order is a reasoning tool, not a promise about the execution plan.
Ordering Grouped Results
GROUP BY does not guarantee display order. Even when a database happens to emit groups in key order, applications must use ORDER BY when order matters.
SELECT department,
COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC, department;
The secondary department sort makes ties deterministic. Top-N reports follow the same pattern: aggregate first, order by the aggregate, define a tie rule, and then apply the database's row-limiting syntax.
Top-Spending Customers
SELECT customer_id,
SUM(total_amount) AS total_spent
FROM orders
WHERE status = 'Completed'
GROUP BY customer_id
ORDER BY total_spent DESC, customer_id
FETCH FIRST 10 ROWS ONLY;
FETCH FIRST, LIMIT, and TOP vary by database. If the requirement includes all customers tied at tenth place, a ranking window function is more appropriate than a simple ten-row limit.
GROUP BY and NULL
Rows whose grouping expression is NULL form one NULL group. This grouping behavior does not mean that NULL = NULL becomes true in ordinary predicates; SQL grouping uses its own “not distinct” concept for group keys.
SELECT COALESCE(department, 'Unassigned') AS department_name,
COUNT(*) AS employee_count
FROM employees
GROUP BY department;
COALESCE can provide a friendly label, but the grouping rule must remain deliberate. Grouping by the original department and displaying a replacement avoids accidentally merging a real department named “Unassigned” with NULL unless that merge is intended.
NULL Values Inside Aggregates
Within every group, most aggregates ignore NULL input values. For salaries 50,000, NULL, and 70,000, COUNT(*) is three, COUNT(salary) is two, SUM is 120,000, AVG is 60,000, MIN is 50,000, and MAX is 70,000. If every salary is NULL, COUNT of salary is zero while SUM, AVG, MIN, and MAX usually return NULL.
Replacing NULL with zero changes both totals and averages. Use COALESCE only when missing genuinely means zero according to the data contract.
Grouping by an Expression
A group key can be a calculated expression rather than a stored column. Reports commonly group timestamps into dates, prices into bands, or raw codes into business categories.
SELECT CASE
WHEN total_amount < 100 THEN 'Small'
WHEN total_amount < 500 THEN 'Medium'
ELSE 'Large'
END AS order_size,
COUNT(*) AS order_count
FROM orders
GROUP BY CASE
WHEN total_amount < 100 THEN 'Small'
WHEN total_amount < 500 THEN 'Medium'
ELSE 'Large'
END;
Repeating a long expression can be hard to maintain. A common table expression or subquery can calculate a named group key first and then group by that simpler column.
Using a CTE for Complex Group Keys
WITH prepared_orders AS (
SELECT order_id,
CAST(order_date AS DATE) AS sales_date,
total_amount
FROM orders
)
SELECT sales_date,
COUNT(*) AS order_count,
SUM(total_amount) AS revenue
FROM prepared_orders
GROUP BY sales_date
ORDER BY sales_date;
The CTE separates preparation from aggregation. This is especially useful when the business key includes timezone conversion, CASE logic, normalized text, or several derived dimensions.
Grouping Dates and Timestamps
Grouping a raw timestamp often creates nearly one group per event because seconds or fractions differ. Daily reporting requires converting the timestamp to a calendar date; monthly reporting requires year and month together.
SELECT EXTRACT(YEAR FROM order_date) AS sales_year,
EXTRACT(MONTH FROM order_date) AS sales_month,
COUNT(*) AS order_count,
SUM(total_amount) AS revenue
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date)
ORDER BY sales_year, sales_month;
Grouping only by month number merges January from every year. That can be useful for seasonality analysis, but it is wrong for a chronological monthly report. The group key must reflect the intended calendar level.
Time Zones and Business Days
If timestamps are stored in UTC while reports use a local business timezone, convert to the reporting zone before deriving the date. An event at 00:30 UTC may belong to the previous local day. Daylight-saving transitions can also create repeated or missing local hours.
Production reports should document the timezone, use half-open time ranges such as >= start and < end, and apply one consistent conversion. Database-specific timezone syntax should be tested around midnight and daylight-saving boundaries.
GROUP BY with JOIN
Grouping often follows a join because descriptive dimensions and measurable facts live in separate tables. A customer summary can join customers to orders and group by the customer key and displayed name.
SELECT c.customer_id,
c.customer_name,
COUNT(o.order_id) AS order_count,
SUM(o.total_amount) AS total_spent
FROM customers c
JOIN orders o
ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name;
The selected non-aggregate customer columns appear in GROUP BY, producing one row per customer identity and name. If names can change historically, the model must define whether the report uses the current name or the name effective when each order occurred.
Including Entities with No Matching Rows
An inner join omits customers without orders. A LEFT JOIN preserves them, but the count expression must count a non-NULL column from the child table rather than the preserved outer row.
SELECT c.customer_id,
c.customer_name,
COUNT(o.order_id) AS order_count,
COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name;
For a customer with no order, COUNT(*) would count the one null-extended join row and misleadingly return one. COUNT(o.order_id) returns zero because a real order ID is absent.
Join Multiplication and Double Counting
GROUP BY cannot repair unintended row multiplication. If one order has three items and two payments, joining orders, items, and payments together can produce six rows for that order. Summing item and payment values after that join inflates both measures.
The reliable approach is to understand cardinality and pre-aggregate each many-side table to one row per order before joining summaries.
WITH item_totals AS (
SELECT order_id,
SUM(quantity * unit_price) AS item_total
FROM order_items
GROUP BY order_id
),
payment_totals AS (
SELECT order_id,
SUM(amount) AS payment_total
FROM payments
GROUP BY order_id
)
SELECT o.order_id,
i.item_total,
p.payment_total
FROM orders o
LEFT JOIN item_totals i ON i.order_id = o.order_id
LEFT JOIN payment_totals p ON p.order_id = o.order_id;
Using DISTINCT inside an aggregate to hide multiplication is risky because legitimate equal values may be removed. Correct the grain and join path instead.
GROUP BY vs DISTINCT
SELECT department FROM employees GROUP BY department and SELECT DISTINCT department FROM employees can return the same values. Their intent differs. DISTINCT removes duplicate output rows, while GROUP BY forms groups so aggregate calculations can be performed.
If uniqueness is the only requirement, DISTINCT communicates it more clearly. Use GROUP BY when each category needs metrics or a HAVING condition.
Selecting Non-Grouped Columns
A grouped result contains one row per group, so every selected expression needs one well-defined value for that group. Selecting department, employee name, and average salary while grouping only by department is ambiguous because a department contains many employee names.
The portable rule is that every selected expression should be a grouping expression or an aggregate expression. Some database engines recognize functional dependencies or permit loose behavior, but relying on an arbitrary-looking non-grouped value produces fragile and misleading SQL.
Conditional Aggregation
CASE expressions inside aggregates calculate several filtered metrics for each group in one query. This is a standard technique for dashboards and status summaries.
SELECT customer_id,
COUNT(*) AS total_orders,
SUM(CASE WHEN status = 'Completed' THEN 1 ELSE 0 END)
AS completed_orders,
SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END)
AS cancelled_orders,
SUM(CASE WHEN status = 'Completed'
THEN total_amount ELSE 0 END)
AS completed_revenue
FROM orders
GROUP BY customer_id;
Conditional aggregation keeps one customer grain while calculating metrics with different conditions. Review ELSE behavior and NULL values carefully, especially when zero and “not applicable” have different meanings.
COUNT(DISTINCT) Within Groups
COUNT(DISTINCT expression) counts unique non-NULL values inside each group. It can report unique products per customer or unique customers per country.
SELECT customer_id,
COUNT(DISTINCT product_id) AS unique_products
FROM order_items
GROUP BY customer_id;
Distinct counts can be computationally expensive on large distributed datasets. Confirm whether exact uniqueness is required and whether the engine offers appropriate indexing, partitioning, or documented approximate methods.
Finding Duplicate Values
Duplicate detection is a practical GROUP BY pattern: group by the columns expected to be unique, count each group, and keep counts greater than one.
SELECT email,
COUNT(*) AS occurrences
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;
For a composite business key, include every key component. Normalization rules such as case folding, trimming, punctuation removal, or canonical phone formatting should match the business definition of a duplicate. Displaying only duplicate keys identifies the problem; cleaning records safely usually requires another query that retrieves complete rows and defines which record survives.
Nested Aggregation
Sometimes one aggregation level must feed another. To find the average department payroll, calculate payroll per department first and then average those department totals.
WITH department_payroll AS (
SELECT department,
SUM(salary) AS payroll
FROM employees
GROUP BY department
)
SELECT AVG(payroll) AS average_department_payroll
FROM department_payroll;
Trying to write an aggregate directly inside another aggregate in one query level is usually invalid. The CTE makes the intermediate grain explicit and testable.
GROUP BY vs Window PARTITION BY
GROUP BY collapses detail rows into one row per group. A window function partitions rows for calculation but preserves every detail row. Choose based on the required output grain.
SELECT employee_id,
department,
salary,
AVG(salary) OVER (PARTITION BY department)
AS department_average
FROM employees;
This window query keeps each employee and places the department average beside that employee. The grouped equivalent returns only departments and averages. Window functions are ideal when detail and group context must appear together.
Grouping Hierarchies
Business dimensions often form hierarchies such as country, state, and city. Grouping by country gives a high-level summary. Grouping by country and state adds detail. Grouping by all three produces city-level rows. Include parent keys because city or state names may not be globally unique.
Changing hierarchy levels can alter totals when dimensions contain unknown values, historical changes, or many-to-many mappings. A semantic model should define each level and how facts connect to it.
ROLLUP, CUBE, and GROUPING SETS
Advanced grouping extensions generate several summary levels in one query. ROLLUP commonly follows a hierarchy and adds subtotals plus a grand total. CUBE creates combinations across dimensions. GROUPING SETS explicitly lists the required combinations.
SELECT region,
product_category,
SUM(sales_amount) AS sales
FROM sales
GROUP BY GROUPING SETS (
(region, product_category),
(region),
(product_category),
()
);
The empty grouping set represents the grand total. Generated subtotal rows can contain NULL placeholders, so supported databases provide functions such as GROUPING() to distinguish subtotal markers from genuine NULL dimension values. Syntax and capabilities vary across engines.
Grouping by Stable Keys and Display Labels
Reports often display a readable label such as department name, product name, or customer segment, but the label is not always the safest group identity. Names can be duplicated, corrected, translated, or reused. Grouping only by department_name could merge two departments that happen to share a label, while grouping only by an internal identifier may not provide enough information for the reader.
A common pattern is to group by the stable key and the associated display label:
SELECT d.department_id,
d.department_name,
COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e
ON e.department_id = d.department_id
GROUP BY d.department_id, d.department_name;
This produces one row per department identity and remains understandable to users. If the database recognizes a functional dependency from a primary key to the name, it may allow a shorter query, but the explicit form is portable and makes the output grain visible during review.
Changing Dimensions and Historical Reports
Grouping becomes more subtle when descriptive values change over time. If a customer moves from the “Standard” segment to “Premium,” should last year's sales appear under the current segment or the segment that applied when each sale occurred? Joining historical orders to the customer's current row answers the first question, while joining through effective-dated history answers the second.
Neither interpretation is universally correct. The report definition must state whether it uses current-state or as-of-event dimensions. Effective-dated joins also need non-overlapping validity ranges; otherwise one fact can match multiple history rows and inflate aggregates. Test records exactly on start and end boundaries.
Normalizing Group Values
Text categories may contain differences in case, whitespace, punctuation, spelling, or encoding. Grouping raw values can create separate groups for 'Completed', 'completed', and 'Completed '. A controlled status code or foreign key is preferable. When cleanup must occur in the query, use an explicit normalization expression and preserve the raw values for data-quality investigation.
SELECT UPPER(TRIM(status)) AS normalized_status,
COUNT(*) AS record_count
FROM orders
GROUP BY UPPER(TRIM(status));
Normalization can also merge values that should remain distinct under a particular collation or business rule. Correct the data at ingestion when possible, enforce allowed values with constraints, and avoid repeatedly applying expensive functions to large reporting tables.
Consistent Snapshots for Grouped Reports
A long-running report may read while transactions continue to insert or update rows. Depending on transaction isolation, different parts of a multi-query dashboard can describe different moments. A count by status might not reconcile with a later grand total even though both queries are individually correct.
Use a consistent database snapshot, an explicit as-of timestamp, or a prepared reporting dataset when reconciliation matters. Record the cutoff in exported reports. If late-arriving events are accepted, define whether past periods are restated and how users can identify refreshed values.
Performance Considerations
Grouping may require sorting, hashing, memory, temporary storage, and data movement. Performance depends on input size, number of distinct groups, aggregate complexity, join cardinality, filters, indexes, partitions, and database architecture.
Filter early with valid WHERE predicates, select only required dimensions and measures, and avoid grouping at unnecessary detail. An index beginning with selective filter columns and followed by grouping or measure columns may help, but no universal index fits every query. Examine the execution plan and test representative data rather than assuming an index guarantees fast aggregation.
Functions applied to indexed timestamps can prevent direct range use. A common strategy is to filter the raw timestamp using a range and then group the qualifying rows by a derived date. For repeated large reports, summary tables or materialized views may be appropriate, provided refresh, correction, deletion, and late-arriving data are handled.
Data Skew and Large Groups
One extremely common group key can dominate processing, especially in distributed systems. A NULL or “Unknown” category may contain most rows while thousands of other groups are small. This skew can overload one worker or consume excessive memory.
Measure group distribution, correct accidental default values, and consider engine-specific partitioning or staged aggregation. Do not split a business group arbitrarily unless partial results can be recombined correctly.
Security and Tenant Isolation
Grouped reports can leak information even when they do not expose individual rows. A count by department or region may reveal activity from another tenant if the tenant predicate is missing. Apply authorization and tenant scope before grouping, ideally through tested data-access controls rather than optional application filters.
Small-group suppression may be required for sensitive demographic or health reports. Suppression rules affect totals and subtotals, so they must be designed with privacy specialists rather than added as an afterthought.
Testing Grouped Queries
Good tests include one group, multiple groups, empty input, one-row groups, duplicate values, NULL group keys, all-NULL measures, zero and negative measures, tied aggregates, and groups exactly at HAVING thresholds. Date reports need month-end, year-end, leap-day, timezone, and daylight-saving cases.
For joins, construct one-to-many and many-to-many examples that expose multiplication. Reconcile group totals with a known overall total when mathematically valid. Also verify that adding a non-qualifying row does not change results and that moving a row from one group updates both affected groups.
Real-World Reporting Pattern
SELECT dimension,
COUNT(*) AS record_count,
SUM(measure) AS total_measure,
AVG(measure) AS average_measure,
MIN(measure) AS minimum_measure,
MAX(measure) AS maximum_measure
FROM table_name
WHERE row_conditions
GROUP BY dimension
HAVING aggregate_conditions
ORDER BY total_measure DESC;
Read this pattern as five decisions: the dimension defines the output grain, the measures define calculations, WHERE defines eligible source rows, HAVING defines eligible groups, and ORDER BY defines presentation. Each decision should map to a stated business rule.
Common Mistakes
- Grouping at a grain that does not match the business question.
- Selecting non-grouped, non-aggregated columns with no defined value.
- Using HAVING for row filters that belong in WHERE.
- Assuming GROUP BY sorts the final result.
- Grouping monthly data without including the year.
- Ignoring the reporting timezone before deriving a date.
- Using
COUNT(*)after a LEFT JOIN when matching children should be counted. - Aggregating after a join that multiplied facts.
- Using DISTINCT to conceal incorrect join cardinality.
- Replacing NULL with zero without a business rule.
- Adding columns to GROUP BY merely to silence an error.
- Using GROUP BY when DISTINCT states the actual requirement.
Production Checklist
- Define what one output row represents.
- List every dimension required to create that grain.
- Confirm the meaning and unit of every measure.
- Apply row eligibility in WHERE and group eligibility in HAVING.
- Decide how NULL group keys and NULL measures should appear.
- Verify join cardinality before aggregation.
- Use the correct count expression for outer joins.
- Convert timestamps to the required timezone and calendar bucket.
- Add deterministic ORDER BY when presentation order matters.
- Compare grouped totals with trusted source results.
- Inspect the execution plan on representative data volume.
- Test authorization, tenant scope, and sensitive small groups.
Interview Questions and Answers
What does GROUP BY do?
It places rows with the same grouping values into groups so aggregate functions can calculate one result per group.
What is the difference between WHERE and HAVING?
WHERE filters individual rows before grouping. HAVING filters groups after aggregate results have been calculated.
Can GROUP BY use multiple columns?
Yes. Every unique combination of those column values becomes a separate group, so the combination defines the result grain.
Does GROUP BY guarantee sorting?
No. Use ORDER BY explicitly whenever output order matters.
How does GROUP BY treat NULL?
Rows with NULL in the same grouping expression are placed in one NULL group, although ordinary NULL equality does not evaluate to true.
Why can joins produce wrong grouped totals?
Joins occur logically before grouping and may multiply fact rows. Aggregates then process the already-multiplied rows. Correct cardinality or pre-aggregate each many-side table.
What is the difference between GROUP BY and PARTITION BY?
GROUP BY collapses rows to one row per group. Window PARTITION BY calculates within groups while preserving individual rows.
Complete Mental Model
Source tables and joins
|
v
WHERE keeps eligible rows
|
v
GROUP BY forms the required grain
|
v
Aggregates calculate each group
|
v
HAVING keeps eligible groups
|
v
SELECT presents dimensions and measures
|
v
ORDER BY controls final presentation
If the final row should represent one department, group by department. If it should represent one customer per month, group by customer and a correctly derived month key. Once the grain is explicit, aggregate choice, filtering, joining, testing, and indexing become much easier to reason about.
Key Takeaway
GROUP BY transforms detailed rows into category-level information. Its syntax is simple, but reliable results depend on defining the correct grain, filtering rows before grouping, filtering aggregates after grouping, handling NULL deliberately, and preventing joins from multiplying facts.
RELIABLE GROUPED RESULT = CORRECT SOURCE ROWS
+ EXPLICIT RESULT GRAIN
+ VALID AGGREGATES
+ SAFE JOIN CARDINALITY
+ DEFINED NULL RULES
+ CORRECT TIME BUCKETS
+ VERIFIED FILTERS
+ DETERMINISTIC PRESENTATION
Before writing a grouped query, state in plain language what one final row represents. That one sentence is the strongest protection against incorrect totals and misleading reports.