SQL GROUP BY — Basics of DATE_TRUNC, STRING

QUESTION 6

Aggregate After Joining — GROUP BY the JOIN Result and Count Children per Parent

JOIN + GROUP BYLEFT JOINCOALESCEJoin aggregation

Background ▼

Real-world data is split across multiple tables. Joining the tables and then applying GROUP BY lets you aggregate children (orders) for each parent (customer). Use LEFT JOIN when parents with no children must remain.

-- Left-join orders onto customers, then aggregate
SELECT   c.name, COUNT(o.order_id), SUM(o.amount)
FROM     customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

How an outer join affects COUNT: a customer with no orders has one NULL-padded joined row. COUNT(*) counts that row, but COUNT(o.order_id) ignores NULL and correctly returns zero. Use COALESCE(SUM(...), 0) to turn a NULL total into zero.

Problem ▼

Join customers and orders, then calculate the number of orders and total amount for each customer. Keep customers with no orders as zero orders and a zero total. Return name, order_count, total_amount, ordered by total_amount descending and name ascending for ties.

Tables used

► customers (3 rows)

customer_id	name
1	Alice
2	Bob
3	Carol

► orders (5 rows)

order_id	customer_id	amount
101	1	1200
102	1	800
103	2	3000
104	2	1500
105	2	500

Expected Output ▼

name	order_count	total_amount
Bob	3	5000
Alice	2	2000
Carol	0	0

Model Answer ▼

SELECT
  c.name,
  COUNT(o.order_id)          AS order_count,  -- count only non-NULL order rows
  COALESCE(SUM(o.amount), 0) AS total_amount  -- replace a NULL sum with 0
FROM      customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id  -- preserve customers with no orders
GROUP BY  c.customer_id, c.name        -- one group per customer
ORDER BY  total_amount DESC, c.name;  -- highest total first; name breaks ties

/* Logical order: JOIN → GROUP BY → COUNT/SUM → ORDER BY. */

Explanation (table transitions & key points) ▼

SELECT
  c.name,
  COUNT(o.order_id) AS order_count,
  COALESCE(SUM(o.amount), 0) AS total_amount
FROM      customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY  c.customer_id, c.name
ORDER BY  total_amount DESC, c.name;

LEGEND ▼

Rows read / loaded

Excluded / hidden data

① FROM customers LEFT JOIN orders ▼

FROM customers LEFT JOIN ordersLeft-join orders onto customers. Carol has no match, so order_id and amount are NULL in her placeholder row.

1 / 4

customer_id	name	order_id	amount
1	Alice	101	1200
1	Alice	102	800
2	Bob	103	3000
2	Bob	104	1500
2	Bob	105	500
3	Carol	NULL	NULL

6 rows (Carol is NULL-padded)

LEARNING POINTS

Join before aggregating: the logical order is FROM/JOIN → GROUP BY → aggregation. The join creates rows first, and grouping and aggregation then operate on those joined rows. Summarizing the children for each parent is the basic aggregate pattern for a one-to-many relationship such as one customer to many orders.

Use LEFT JOIN plus COUNT(child_column) to report zero correctly: a customer with no orders receives a NULL-padded joined row. COUNT(*) counts that row as one, whereas COUNT(o.order_id) ignores NULL and reports zero. When counting children, count a column from the child table.

Use COALESCE to normalize NULL to zero: SUM over a group with no child values returns NULL, not zero. COALESCE(SUM(o.amount), 0) converts that NULL so “no orders” appears as the intuitive total of zero.

ANTI-PATTERNS

Counting with COUNT(*): after a LEFT JOIN, this incorrectly reports that Carol has one order because it counts her NULL-padded row. COUNT(o.order_id) excludes that row and correctly returns zero.

Using INNER JOIN and losing parents with no children: if the requirement includes customers with zero orders, INNER JOIN removes Carol entirely. Choose LEFT JOIN whenever every parent must remain.

Field Notes: Parent-driven LEFT JOIN is the standard one-to-many aggregate

Customer/order, article/comment, and product/review reports all follow the same pattern: put the parents that must remain in FROM, LEFT JOIN the children, aggregate child columns, and use COALESCE for missing totals. If a child-side filter must still preserve parents with no matching children, put it in the JOIN's ON clause; a WHERE filter would turn the outer join into an effective inner join and remove those parents.

QUESTION 7

Group by Period — Use DATE_TRUNC to Aggregate Dates by Month

DATE_TRUNCGROUP BY expressionTime-series aggregationPeriod aggregation

Background ▼

To summarize daily records by month or week, truncate each date to the chosen granularity with DATE_TRUNC and GROUP BY that expression. The important point is that GROUP BY can use a computed expression, not just a stored column.

-- Map dates to their month start so rows in the same month share one key
DATE_TRUNC('month', sale_date)  -- 2024-01-05 → 2024-01-01
DATE_TRUNC('day', ts)          -- remove the time portion

Grouping by an expression: dates such as January 5 and January 20 all become the same month-start value, 2024-01-01. GROUP BY collects rows by that truncated value, which produces a monthly aggregate. Repeat the SELECT expression in GROUP BY, or use its ordinal with GROUP BY 1.

Problem ▼

From sales, calculate the number of sales and total amount for each month. Return month, sales_count, total_amount in ascending month order.

Tables used

► sales (6 rows)

sale_id	sale_date	amount
1	2024-01-05	1000
2	2024-01-20	1500
3	2024-02-03	2000
4	2024-02-15	500
5	2024-02-28	1000
6	2024-03-10	3000

Expected Output ▼

month	sales_count	total_amount
2024-01-01	2	2500
2024-02-01	3	3500
2024-03-01	1	3000

Model Answer ▼

SELECT
  DATE_TRUNC('month', sale_date)::date AS month,  -- return the month-start date
  COUNT(*)    AS sales_count,             -- sales in the month
  SUM(amount) AS total_amount             -- amount in the month
FROM     sales
GROUP BY DATE_TRUNC('month', sale_date)     -- same expression as SELECT
ORDER BY month;                             -- chronological order

/* Logical order: FROM → DATE_TRUNC → GROUP BY → aggregate → ORDER BY. */

Explanation (table transitions & key points) ▼

SELECT
  DATE_TRUNC('month', sale_date) AS month,
  COUNT(*) AS sales_count,
  SUM(amount) AS total_amount
FROM     sales
GROUP BY DATE_TRUNC('month', sale_date)
ORDER BY month;

LEGEND ▼

Rows read / loaded

① FROM sales (6 rows) ▼

FROM salesRead six daily sales rows before mapping their dates to months.

1 / 4

sale_id	sale_date	amount
1	2024-01-05	1000
2	2024-01-20	1500
3	2024-02-03	2000
4	2024-02-15	500
5	2024-02-28	1000
6	2024-03-10	3000

6 daily rows read

LEARNING POINTS

GROUP BY accepts expressions as well as columns: grouping by the result of DATE_TRUNC('month', sale_date) creates a reporting dimension that does not exist in the source data. This is what makes monthly and weekly aggregation possible.

Truncation creates equal grouping keys: DATE_TRUNC maps every date in a month to the same month-start value, so those rows form one group. Change 'month' to 'week', 'day', or 'quarter' to change the reporting granularity.

Repeat the expression or use GROUP BY 1: the SELECT alias may not yet be available when GROUP BY is evaluated, so repeating the expression or using its ordinal is reliable. PostgreSQL permits the alias too, but repeating the expression is more portable.

ANTI-PATTERNS

Grouping by the raw date: GROUP BY sale_date creates separate groups whenever the dates differ, producing daily rather than monthly results. Group by the truncated expression instead.

Grouping only by a month-number string: an expression such as TO_CHAR(sale_date, 'MM') can mix the same month from different years and can sort chronologically incorrect as text. Group by the date-typed DATE_TRUNC result including the year.

Field Notes: Time-series dashboards differ mainly in granularity

Sales trends, registration growth, and error counts all use DATE_TRUNC-based period grouping. Replacing the first argument switches the same aggregate logic among daily, weekly, monthly, and quarterly views. If you need a display label, format the result with something like TO_CHAR(month, 'YYYY-MM'), but keep the date value for sorting and joins so chronological order remains correct.

QUESTION 8

Group by Category — Bin Values with CASE and Aggregate by Category

CASE expressionBinningGROUP BY expressionCategory aggregation

Background ▼

To summarize a continuous value by ranges such as price bands or age groups, create a category with CASE and GROUP BY that expression.

-- Assign each price to one of three bands
CASE
  WHEN price < 500  THEN 'Low'
  WHEN price < 5000 THEN 'Mid'   -- 500–4999 because the first branch already failed
  ELSE 'High'
END

CASE uses the first matching branch: WHEN clauses are evaluated top to bottom. Order the boundaries carefully so that every value belongs to exactly one band.

Problem ▼

From products, calculate the product count and average price for each price band: Low below 500, Mid from 500 through 4999, and High from 5000. Return price_band, product_count, avg_price, rounding the average to an integer and sorting by avg_price ascending.

Tables used

► products (6 rows)

product_id	product	price
1	Pen	100
2	Notebook	300
3	Bag	1500
4	Lamp	2500
5	Chair	8000
6	Desk	12000

Expected Output ▼

price_band	product_count	avg_price
Low	2	200
Mid	2	2000
High	2	10000

Model Answer ▼

SELECT
  CASE
    WHEN price < 500  THEN 'Low'   -- below 500
    WHEN price < 5000 THEN 'Mid'   -- 500 through 4999
    ELSE 'High'                  -- 5000 or more
  END AS price_band,
  COUNT(*)             AS product_count, -- products in each price band
  ROUND(AVG(price), 0) AS avg_price -- average price per band, rounded to an integer
FROM products
GROUP BY CASE WHEN price < 500 THEN 'Low' WHEN price < 5000 THEN 'Mid' ELSE 'High' END -- repeat the SELECT CASE expression; GROUP BY price_band also works in PostgreSQL
ORDER BY avg_price; -- lowest average price first

/* Logical order:
   1. FROM products → read the rows
   2. CASE → calculate price_band for every row
   3. GROUP BY the CASE result → form one group per price band
   4. COUNT(*) / ROUND(AVG(price), 0) → calculate count and average
   5. ORDER BY avg_price → sort by average price */

Explanation (table transitions & key points) ▼

SELECT
  CASE WHEN price < 500 THEN 'Low'
       WHEN price < 5000 THEN 'Mid'
       ELSE 'High' END AS price_band,
  COUNT(*) AS product_count,
  ROUND(AVG(price), 0) AS avg_price
FROM     products
GROUP BY price_band
ORDER BY avg_price;

LEGEND ▼

Rows read / loaded

① FROM products (6 rows) ▼

FROM productsRead six localized products with continuous price values.

1 / 4

product_id	product	price
1	Pen	100
2	Notebook	300
3	Bag	1500
4	Lamp	2500
5	Chair	8000
6	Desk	12000

6 rows read (continuous prices)

LEARNING POINTS

CASE creates a new grouping dimension: derive a category that is absent from the source—such as a price band, age group, or rating tier—and use it as the grouping key. This standard reporting technique folds continuous values into meaningful bins.

CASE stops at the first matching branch: WHEN clauses are evaluated top to bottom. The second price < 5000 branch means 500–4999 because values below 500 were already handled by the first branch. Boundary order determines the result.

Group by the same expression: repeat the SELECT CASE expression in GROUP BY. PostgreSQL also permits GROUP BY price_band or GROUP BY 1, which can avoid duplicating a long CASE expression.

ANTI-PATTERNS

Putting the wider condition first: if WHEN price < 5000 THEN 'Mid' comes first, the 100 and 300 values that belong in Low are absorbed into Mid and the Low group disappears. Put narrower, lower boundaries first.

Leaving gaps or overlaps: inconsistent >= and > boundaries, or a missing ELSE, can leave rows unclassified or count them twice in separately written conditions. Design bands to be mutually exclusive and collectively exhaustive.

Field Notes: CASE binning is the first step toward a histogram

Price-band product counts, customer-spend ranges, and score-tier user counts are all histograms built by creating CASE bands and counting them. Change the thresholds to tune the granularity or create business-specific categories such as free/paid and beginner/advanced. For equal-width bins consider width_bucket(); frequently reused business bands can become generated columns.

QUESTION 9

Detect Duplicates — Find Duplicate Data with GROUP BY and HAVING COUNT(*) > 1

HAVING COUNTDuplicate detectionData qualityPost-aggregate filtering

Background ▼

“Has the same email address been registered more than once?” Duplicate detection is a classic use of grouping. GROUP BY the column to inspect, then keep only groups containing at least two rows with HAVING COUNT(*) > 1.

-- Count each email and keep counts of two or more: the duplicates
SELECT email, COUNT(*) AS cnt FROM users GROUP BY email HAVING COUNT(*) > 1;

Group the column that should be unique: the standard pattern is “GROUP BY the intended unique key → HAVING COUNT(*) > 1.” It is common before adding a UNIQUE constraint and when checking data quality after an import. To inspect duplicates of a composite key, list every key column as in GROUP BY col_a, col_b.

Problem ▼

Return each email registered more than once and its count from users. Return email, cnt, ordered by cnt descending and email ascending.

Tables used

► users (6 rows)

user_id	email
1	a@x.com
2	b@x.com
3	a@x.com
4	c@x.com
5	b@x.com
6	a@x.com

Expected Output ▼

email	cnt
a@x.com	3
b@x.com	2

Model Answer ▼

SELECT email, COUNT(*) AS cnt -- registrations per email
FROM users
GROUP BY email -- group the value that should be unique
HAVING COUNT(*) > 1 -- duplicate groups only
ORDER BY cnt DESC, email; -- highest count first; email breaks ties

/* Logical order:
   1. FROM users → read the rows
   2. GROUP BY email → form one group per email
   3. COUNT(*) → count each group
   4. HAVING COUNT(*) > 1 → keep duplicate groups only
   5. ORDER BY cnt DESC, email → sort by duplicate count */

Explanation (table transitions & key points) ▼

SELECT
  email,
  COUNT(*) AS cnt
FROM     users
GROUP BY email
HAVING   COUNT(*) > 1
ORDER BY cnt DESC, email;

LEGEND ▼

Rows read / loaded

① FROM users (6 rows) ▼

FROM usersRead six rows; two localized email values repeat.

1 / 5

user_id	email
1	a@x.com
2	b@x.com
3	a@x.com
4	c@x.com
5	b@x.com
6	a@x.com

6 rows read

LEARNING POINTS

Memorize the duplicate-detection pattern: GROUP BY the column that should be unique, then apply HAVING COUNT(*) > 1. The returned count shows exactly how many rows share each value, making the result directly useful for data-quality checks.

Counts require HAVING, not WHERE: COUNT(*) does not exist until after grouping and aggregation. Raw-row predicates belong in WHERE; aggregate predicates belong in HAVING.

Composite-key duplicates use the same pattern: to find repeated name-and-birth-date combinations, use GROUP BY name, birth_date HAVING COUNT(*) > 1. It simply combines multi-column grouping with HAVING.

ANTI-PATTERNS

Writing WHERE COUNT(*) > 1: this is invalid because WHERE runs before grouping, when COUNT has not yet been calculated. Put count-based filters in HAVING.

Using DISTINCT merely to hide duplicates: SELECT DISTINCT email collapses repeated values but does not reveal which values repeat or how often. Use GROUP BY plus HAVING when the goal is to detect and quantify duplicates.

Field Notes: Check before adding a UNIQUE constraint

Before making email or a membership number UNIQUE, run this query to verify that existing rows do not conflict; otherwise adding the constraint will fail. To identify the actual records as well as the repeated value, add the Q10 pattern STRING_AGG(user_id::text, ', '). That gives a practical data-cleaning sequence: detect the duplicate, identify its rows, then fix them.

QUESTION 10

Concatenate Values — Combine Values Within Each Group with STRING_AGG

STRING_AGGORDER BY within aggregateARRAY_AGGString aggregation

Background ▼

The final aggregation pattern in this set aggregates text rather than numbers. STRING_AGG concatenates multiple values in a group into one delimited string, allowing a list of the group's members to fit in one row.

STRING_AGG(student, ', ' ORDER BY student) -- concatenate names alphabetically
STRING_AGG(DISTINCT tag, ',') -- remove duplicates
ARRAY_AGG(student) -- aggregate into an array instead of text

ORDER BY inside the aggregate controls concatenation order: without it, the order is unspecified. The second argument is the delimiter. COUNT and SUM fold many values into one number; STRING_AGG folds many values into one string.

Problem ▼

From enrollments, calculate each course's student count and an alphabetically ordered, comma-separated student list. Return course, student_count, students, ordered by student_count descending and course ascending for ties.

Tables used

► enrollments (6 rows)

id	course	student
1	Math	Bob
2	Math	Alice
3	Math	Carol
4	Science	Dave
5	Science	Alice
6	Art	Bob

Expected Output ▼

course	student_count	students
Math	3	Alice, Bob, Carol
Science	2	Alice, Dave
Art	1	Bob

Model Answer ▼

SELECT course,
  COUNT(*) AS student_count, -- students per course
  STRING_AGG(student, ', ' ORDER BY student) AS students -- alphabetical name list
FROM enrollments
GROUP BY course -- one group per course
ORDER BY student_count DESC, course; -- largest course first; course breaks ties

/* Logical order:
   1. FROM enrollments → read the rows
   2. GROUP BY course → form one group per course
   3. COUNT / STRING_AGG → calculate the count and ordered name list
   4. ORDER BY student_count DESC, course → sort by enrollment size */

Explanation (table transitions & key points) ▼

SELECT
  course,
  COUNT(*) AS student_count,
  STRING_AGG(student, ', ' ORDER BY student) AS students
FROM     enrollments
GROUP BY course
ORDER BY student_count DESC, course;

LEGEND ▼

Rows read / loaded

① FROM enrollments (6 rows) ▼

FROM enrollmentsRead six localized enrollments, one per student-course registration.

1 / 4

id	course	student
1	Math	Bob
2	Math	Alice
3	Math	Carol
4	Science	Dave
5	Science	Alice
6	Art	Bob

6 rows read

LEARNING POINTS

STRING_AGG is an aggregate function for text: COUNT and SUM fold a group into one number; STRING_AGG folds the group's strings into one concatenated value. A complete member list can therefore appear in a single report cell.

ORDER BY inside the aggregate controls concatenation order: in STRING_AGG(student, ', ' ORDER BY student), the inner ORDER BY determines the name sequence. Without it the order is unspecified and may change between executions. The second argument, ', ', is the delimiter.

DISTINCT and ARRAY_AGG provide related output forms: use STRING_AGG(DISTINCT student, ', ') to remove duplicates before concatenation, or ARRAY_AGG(student) when the caller needs an array rather than a string.

ANTI-PATTERNS

Depending on unspecified order: STRING_AGG(student, ', ') does not guarantee a sequence. If sequence carries meaning, always put ORDER BY inside the aggregate.

Confusing SQL dialects: MySQL calls this function GROUP_CONCAT, and Oracle calls it LISTAGG. STRING_AGG is PostgreSQL and SQL Server syntax, so check both the function name and delimiter syntax when porting the query.

Field Notes: From detection to identification

STRING_AGG is useful for tag lists, assignee lists, related-product IDs, and CSV exports. Combined with duplicate query, adding STRING_AGG(user_id::text, ', ') returns both each duplicate email and the IDs of its actual records. GROUP BY can now combine counts, sums, conditional aggregates, joins, periods, bins, and text aggregation to produce most practical summaries from detail data.