SQL Data Types, Aggregations, and Grouping Rules

SQL Data Types, Explicit Casting, and Precision

  • Northwind Database Architecture:

    • Northwind is a relational database representing a fictional retail business.
    • Key tables include: customers, orders, order_details, products, categories, and employees.
    • The order_details table contains key attributes such as order_id, product_id, unit_price, quantity, and discount.

  • Integer Data Type Variations:

    • integer (or standard int): Standard 4-byte integer used for whole numbers.
    • smallint: A smaller integer variation designed with a strict limit on maximum value to minimize storage footprint.
    • Historically, smallint and tinyint were heavily utilized in older database schemas to conserve expensive disk storage and memory.
    • In modern database systems equipped with abundant, low-cost storage and compute power, specifying smallint is rarely necessary, and standard integer or higher is preferred.

  • Floating-Point vs. Exact Numeric Data Types:

    • real: A floating-point numeric type. It is inherently imprecise due to how binary floating-point calculations are handled under IEEE standards.
    • numeric / decimal: Exact numeric types that preserve fixed decimal precision. In SQL, numeric and decimal are completely equivalent; creating a column as numeric produces the exact same result as defining it as decimal.
    • Legacy floating-point data types like real and double precision are holdovers from 1980s and 1990s database architecture when storage efficiency outweighed strict mathematical exactness.

  • Arithmetic Precision and Explicit Casting:

    • Multiplying fixed numeric types (e.g., unit_price) by integer types (e.g., quantity) in SQL queries can produce unexpected floating-point artifacts or excessive decimal places.
    • The Standard CAST() Function:
      • CAST(expression AS data_type) is the ANSI SQL standard function supported across all relational database management systems (RDBMS) including PostgreSQL, MySQL, Microsoft SQL Server, and Oracle.
      • Syntax example: CAST(unit_price * quantity AS numeric(10,2)).
      • In numeric(10,2), the first argument (1010) defines the scale (total number of stored digits), and the second argument (22) defines the precision (number of digits to the right of the decimal point). This allows values up to 99,999,999.9999,999,999.99

  • PostgreSQL Double Colon (::) Operator:

    • PostgreSQL provides a non-standard shorthand notation for type conversion known as the double colon or "bang bang" syntax.
    • Syntax example: (unit_price * quantity)::numeric(10,2).
    • This eliminates the need for explicit CAST() phrasing and yields the exact same execution state in a PostgreSQL environment.

  • The ROUND() Function Constraints:

    • Syntax: ROUND(numeric_expression, decimal_places).
    • ROUND() explicitly requires a numeric data type input when passing a secondary decimal place argument.
    • Aggregate functions such as AVG() over certain numeric fields return a double precision floating-point data type. Executing ROUND(AVG(freight), 2) directly on a double precision output will fail with a syntax/type error because ROUND() cannot process precision arguments for floating-point types.
    • To resolve this, explicitly cast the aggregated result to numeric before wrapping it in ROUND():         ROUND(CAST(AVG(freight) AS numeric),2)\text{ROUND}(\text{CAST}(\text{AVG}(\text{freight}) \text{ AS numeric}), 2)
    • If numeric is declared without explicit scale or precision parameters (e.g., ::numeric), PostgreSQL dynamically assigns the most permissive numeric type based on the input data.

Aggregating Data with GROUP BY

  • Definition and Core Function:

    • The GROUP BY clause collapses rows sharing common categorical values into single summary rows, enabling the computation of aggregate statistics across discrete categories.

  • The Split-Apply-Combine Pattern:

    • The database engine processes GROUP BY operations through a three-stage execution pattern:
      1. Split: The input table is partitioned into distinct sub-tables based on the unique categorical values of the specified column(s).
      2. Apply: An aggregate transformation function (e.g., COUNT(), SUM(), AVG(), MIN(), MAX()) is evaluated independently on each split partition.
      3. Combine: The calculated summary results are assigned to their respective categorical row labels and merged into a single output dataset.

Filtering Data: WHERE vs. HAVING Clauses

  • Filtering Sequence and Execution Order:

    • WHERE filters individual rows before any grouping or aggregation takes place.
    • HAVING filters group summary rows after the GROUP BY clause has aggregated the data.

  • Logical SQL Execution Order:

    1. FROM (identifies source tables)
    2. WHERE (filters raw individual rows)
    3. GROUP BY (collapses filtered rows into categorical partitions)
    4. HAVING (filters summary rows based on aggregate metrics)
    5. SELECT (evaluates expressions, functions, and column aliases)
    6. ORDER BY (sorts the final output set)

  • Aggregate Filter Violations:

    • Attempting to use an aggregate function inside a WHERE clause (e.g., WHERE COUNT(*) > 10) causes an error: aggregate functions are not allowed in WHERE clause.
    • Aggregate conditions must strictly be placed inside the HAVING clause (e.g., HAVING COUNT(*) > 10).

Common GROUP BY Errors and Dialect Features

  • The Non-Aggregated Column Rule:

    • Every non-aggregated column present in the SELECT list must explicitly appear in the GROUP BY clause.
    • Error Example: Selecting category_id, COUNT(*) without declaring GROUP BY category_id triggers the error: column products.category_id must appear in the GROUP BY clause or be used in an aggregate function.
    • Standalone Exception: COUNT(*) can be evaluated without a GROUP BY clause only if no non-aggregated categorical columns exist in the SELECT list.

  • Referencing Column Aliases:

    • Standard SQL rules prohibit referencing SELECT column aliases inside GROUP BY or HAVING clauses because SELECT is logically evaluated after GROUP BY and HAVING.
    • PostgreSQL Dialect Shortcut: PostgreSQL, MySQL, and SQLite allow grouping directly by column aliases defined in SELECT (e.g., GROUP BY year). Microsoft SQL Server does not support alias grouping and requires re-stating the full expression (e.g., GROUP BY EXTRACT(year FROM order_date)).
    • HAVING Restriction: Even in PostgreSQL, column aliases cannot be referenced inside the HAVING clause; full aggregate expressions must be re-stated explicitly (e.g., HAVING COUNT(*) > 10).

  • The GROUP BY ALL Syntax:

    • A modern database extension supported by select modern engines that automatically includes all non-aggregated SELECT columns into the GROUP BY clause without requiring manual enumeration.

Advanced Aggregations: ROLLUP and CUBE

  • Overview and Use Cases:

    • ROLLUP and CUBE extend GROUP BY by automatically calculating hierarchical subtotals and grand totals directly within SQL.
    • Useful for generating reporting datasets or pre-aggregated summary tables downstream for business intelligence tools like Power BI or Tableau.

  • GROUP BY ROLLUP(a, b):

    • Calculates hierarchical subtotals based on the left-to-right order of listed columns.
    • For ROLLUP(ship_country, ship_city), it returns:
      1. Subtotals for each unique (ship_country, ship_city) pair.
      2. Subtotals for each ship_country (where ship_city is NULL).
      3. A grand total across all rows (where both ship_country and ship_city are NULL).

  • GROUP BY CUBE(a, b):

    • Calculates a complete cross-tabulation of all possible column combinations.
    • For CUBE(ship_country, ship_city), it returns:
      1. Subtotals for each (ship_country, ship_city) pair.
      2. Subtotals for each ship_country on its own.
      3. Subtotals for each ship_city on its own (independent of country).
      4. A grand total row (where both columns are NULL).
    • CUBE always produces a larger dataset with more rows than ROLLUP due to unconstrained permutation processing.

  • Formatting Subtotals with COALESCE():

    • COALESCE(val1, val2) returns the first non-null argument.
    • Because ROLLUP and CUBE insert NULL values to represent aggregate levels, wrapping categorical outputs in COALESCE() replaces NULL markers with human-readable labels like 'Grand Total' or 'Subtotal'.

Practical SQL Exercises and Queries

  • Exercise 1.1: Standard Explicit Casting with CAST()

    • Requirement: Calculate line total (unit_price * quantity) explicit cast to numeric(10,2) limited to 10 rows.
    • Query: sql SELECT order_id, product_id, CAST(unit_price * quantity AS numeric(10,2)) AS line_total FROM order_details LIMIT 10;         
    • Expected Verification: Order ID 10248 returns a line_total of 168.00 (or 168).

  • Exercise 1.2: PostgreSQL Syntax (::) Conversion

    • Requirement: Re-write Exercise 1.1 using PostgreSQL double-colon casting syntax.
    • Query: sql SELECT order_id, product_id, (unit_price * quantity)::numeric(10,2) AS line_total FROM order_details LIMIT 10;         

  • Exercise 1.3: Date Extraction and Aggregation

    • Requirement: Count the number of orders placed in each year.
    • ANSI Standard Query: sql SELECT EXTRACT(year FROM order_date) AS year, COUNT(*) AS order_count FROM orders GROUP BY EXTRACT(year FROM order_date) ORDER BY year;         
    • PostgreSQL Shorthand Query: sql SELECT EXTRACT(year FROM order_date) AS year, COUNT(*) AS order_count FROM orders GROUP BY year ORDER BY year;         
    • Benchmark Results:
      • Year 2022: 152 orders
      • Year 2023: 408 orders
      • Year 2024: 270 orders

  • Exercise 2.1: Basic Categorical Aggregation

    • Requirement: Group products by category_id, count total products per category, and sort descending by count.
    • Query: sql SELECT category_id, COUNT(*) AS product_count FROM products GROUP BY category_id ORDER BY product_count DESC;         

  • Exercise 2.2: Aggregated Filtering with HAVING

    • Requirement: Filter product categories to include only those with more than 10 products.
    • Query: sql SELECT category_id, COUNT(*) AS product_count FROM products GROUP BY category_id HAVING COUNT(*) > 10 ORDER BY product_count DESC;         

  • Exercise 2.3: Nested Aggregate Math and Rounding

    • Requirement: Calculate rounded total revenue per product accounting for discount rates.
    • Mathematical Formula:         Revenue=SUM(unit_price×quantity×(1−discount))\text{Revenue} = \text{SUM}\left(\text{unit\_price} \times \text{quantity} \times (1 - \text{discount})\right)
    • Query: sql SELECT product_id, ROUND(CAST(SUM(unit_price * quantity * (1 - discount)) AS numeric), 2) AS revenue FROM order_details GROUP BY product_id;         

  • Exercise 2.5: Aggregating Distinct Values

    • Requirement: Count total orders alongside distinct customer counts handled by each employee.
    • Query: sql SELECT employee_id, COUNT(DISTINCT customer_id) AS distinct_customers, COUNT(*) AS total_orders FROM orders GROUP BY employee_id;         
    • Benchmark Metric: Employee ID 4 managed 75 distinct customers across 156 total orders.

  • Exercise 3.1: Subtotals via ROLLUP

    • Requirement: Calculate order counts for cities in USA, Canada, and Mexico with subtotals and grand totals.
    • Query: sql SELECT COALESCE(ship_country, 'Grand Total') AS ship_country, COALESCE(ship_city, 'Subtotal') AS ship_city, COUNT(*) AS order_count FROM orders WHERE ship_country IN ('USA', 'Canada', 'Mexico') GROUP BY ROLLUP(ship_country, ship_city) ORDER BY ship_country NULLS FIRST;         
    • Result Metric: Exactly 20 summary rows returned.

  • Exercise 3.2: Cross-Tabulation via CUBE

    • Requirement: Calculate order metrics across all permutations of ship country and city.
    • Query: sql SELECT COALESCE(ship_country, 'Grand Total') AS ship_country, COALESCE(ship_city, 'Subtotal') AS ship_city, COUNT(*) AS order_count FROM orders WHERE ship_country IN ('USA', 'Canada', 'Mexico') GROUP BY CUBE(ship_country, ship_city);         

Questions & Technical Discussion

  • Clarification on Floating-Point Imprecision vs. Numeric:

    • Question: What is the definition and technical nature of double precision?
    • Explanation: double precision is an IEEE floating-point data type stored in binary format. Because decimal numbers cannot always be represented exactly in binary floating-point arithmetic, mathematical operations are inherently imprecise compared to exact numeric/decimal data types. In enterprise database design, exact numeric fields should always be selected over floating-point types for monetary and precise values.

  • Query Formatting Strategy: Leading vs. Trailing Commas:

    • Leading Comma Convention: sql SELECT order_id , product_id , unit_price FROM order_details;         
    • Technical Rationale: Placing commas at the beginning of subsequent lines allows developer comments (--) to disable or comment out the final line in a SELECT list without leaving a stray trailing comma that causes a syntax syntax execution failure.