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

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

DATE_TRUNC maps each date to the first day of its month.

Expected output (month ascending):

Six rows collapse into three groups: January 2, February 3, and March 1.

SELECT
  DATE_TRUNC('month', sale_date) AS month,  -- month-start grouping key
  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. */

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

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.

The localized products form two rows in each band: Pen/Notebook, Bag/Lamp, and Chair/Desk.

Expected output (avg_price ascending):

Low=(100+300)/2=200, Mid=(1500+2500)/2=2000, High=(8000+12000)/2=10000.

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 */

“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;

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

The localized addresses occur 3, 2, and 1 times; the single occurrence is not a duplicate.

Expected output (cnt descending):

The address occurring once fails HAVING and is excluded.

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 */

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

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.

The localized courses contain 3, 2, and 1 students.

Expected output (student_count descending):

Math input order Bob, Alice, Carol becomes Alice, Bob, Carol.

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 */