SQL Reporting and Data Aggregation

The key to SQL reporting is breaking apart the request to find out what information is actually needed and where it has to come from. The course project in the previous lesson demonstrated this directly: Customer and Inventory weren't needed at all, despite being available. That's a genuinely common pattern in reporting work, people ask for what they want without knowing, or particularly caring, how the data actually gets retrieved; the job is turning that request into the right query, not the other way around.

This lesson focuses specifically on one recurring piece of that job: reporting requests are almost always asking for aggregated numbers, totals, counts, averages, not raw transaction-level detail, and deciding where that aggregation actually happens, in a view, in a physically stored table, or inside the report tool itself, is a real design decision with real tradeoffs, not an afterthought.

The techniques covered throughout this course feed into more than just hand-written SQL*Plus reports. The same queries can drive an Access report writer, populate an Excel pivot table, or supply a custom report generator built for a specific application. The query is the foundation regardless of what eventually displays its result.

Building toward any of these starts the same way: study the database layout, identify the keys connecting tables, and work out the relationships between them. Then break apart the actual reporting requirement, starting from the most specific piece of information needed and working outward toward the more general. That's exactly the order this course's own techniques tend to get applied in: functions, GROUP BY, and everything else covered so far.

Reporting applications generally need aggregated data, and a view is a natural way to make that aggregation look like it's simply stored in the database, without actually duplicating anything. This example, adapted from a well-known SQL textbook's MySQL-based original into Oracle syntax, illustrates a monthly report showing each customer's active account count and balance total. Two customer types share this one view: business customers, whose display name comes from a business table, and individual customers, whose name gets built by concatenating a first and last name from an individual table. Rather than writing two separate queries for two separate customer types, a CASE expression with a correlated subquery on each branch picks the right source and produces one unified cust_name column regardless of which type a given customer happens to be:

CREATE VIEW customer_totals_vw
    (cust_id, cust_type_cd, cust_name, tot_active_accounts, tot_balance)
AS
SELECT cst.cust_id, cst.cust_type_cd,
    CASE
        WHEN cst.cust_type_cd = 'B' THEN
            (SELECT bus.name FROM business bus WHERE bus.cust_id = cst.cust_id)
        ELSE
            (SELECT CONCAT(ind.fname, ' ', ind.lname)
             FROM individual ind
             WHERE ind.cust_id = cst.cust_id)
    END AS cust_name,
    SUM(CASE WHEN act.status = 'ACTIVE' THEN 1 ELSE 0 END) AS tot_active_accounts,
    SUM(CASE WHEN act.status = 'ACTIVE' THEN act.avail_balance ELSE 0 END) AS tot_balance
FROM customer cst
JOIN account act ON act.cust_id = cst.cust_id
GROUP BY cst.cust_id, cst.cust_type_cd;

Two details are worth calling out directly. First, the view's declared column list now matches what the query actually computes, tot_active_accounts and tot_balance, rather than the more generic num_accounts and tot_deposits that appeared in the original; a view's declared name should describe what it actually holds, and this one specifically counts only active accounts, not every account a customer has ever opened.

Second, CONCAT(ind.fname, ' ', ind.lname) uses three arguments, and this is genuinely valid, current Oracle syntax: Oracle AI Database 26ai's CONCAT function accepts two or more arguments, not the strict two-argument limit older Oracle versions enforced. Application developers querying this view see one simple row per customer; none of the CASE logic, the correlated subqueries picking a business or individual name, or the conditional aggregation counting only active accounts, is something they ever need to write or even see.

Querying the finished view makes that simplicity concrete:

SELECT * FROM customer_totals_vw
WHERE cust_id IN (1001, 1002, 1003);

CUST_ID CUST_TYPE_CD CUST_NAME        TOT_ACTIVE_ACCOUNTS TOT_BALANCE
------- ------------ ---------------- -------------------- -----------
1001    I            Wei Chen                            2     4820.50
1002    B            Riverside Supply                     3    18450.00
1003    I            Adaeze Okafor                        1      950.25

Whoever wrote this query never had to know that cust_type_cd = 'B' triggers a lookup against business while anything else triggers a lookup against individual, or that the balance total deliberately excludes any account that isn't currently active. All of that logic lives once, inside the view's own definition, exactly the abstraction principle covered earlier in this course applied directly to a reporting scenario.

Data aggregation is a standard, common technique across SQL-based reporting generally, and a view is only one of several ways to present it. A few related patterns show up repeatedly:

• Regular views built on GROUP BY and aggregate functions, exactly like customer_totals_vw above. This is almost always the right starting point: no extra storage, no refresh schedule to manage, and Oracle's own query merging keeps the performance cost close to what the equivalent hand-written query would cost anyway.
• Materialized views, which store the aggregated result physically and refresh it on a schedule, trading some currency for query speed, covered in depth earlier in this course. Reach for this specifically once a regular view's live recomputation becomes a measurable performance problem, not by default.
• Dedicated summary tables, populated by a scheduled job or an ETL process rather than computed on demand at all. This looks similar to a materialized view from the outside, a physically stored, precomputed result, but the refresh logic lives in whatever external job populates it, rather than being managed by the database's own REFRESH mechanism.
• Aggregation performed at the report layer itself, when the underlying source stays fully detailed and whatever tool is generating the report handles the summing or counting on its own. This keeps the database simple at the cost of pushing computation into a layer that's often less efficient at it, and duplicating the same aggregation logic across every report that needs it rather than centralizing it once.

A monthly summary using Oracle's own date-truncation function follows the same shape as the view above:

CREATE VIEW v_monthly_customer_summary AS
SELECT
    customer_id,
    TRUNC(deposit_date, 'MM') AS report_month,
    COUNT(account_id) AS number_of_accounts,
    SUM(deposit_amount) AS total_deposits
FROM accounts
GROUP BY customer_id, TRUNC(deposit_date, 'MM');

TRUNC(deposit_date, 'MM') rounds a date down to the first of its month, already covered in an earlier lesson's monthly revenue example; it's the direct Oracle equivalent of what other database products call DATE_TRUNC, a different function name for the same underlying idea. A reporting application querying this view for a given month never needs to know that truncation logic exists at all; it just selects from the view.

Whichever pattern is used, the underlying relationship is the same: aggregation and reporting work together naturally, and a view is simply one convenient way to keep that aggregation logic out of the report author's hands entirely.

Seeing the materialized-view alternative next to this regular view makes the tradeoff concrete rather than abstract. The regular view above recomputes its SUM and TRUNC logic every time it's queried; a materialized version of the identical query instead stores that result physically and refreshes it on a schedule:

CREATE MATERIALIZED VIEW mv_monthly_customer_summary
REFRESH COMPLETE ON DEMAND
AS
SELECT
    customer_id,
    TRUNC(deposit_date, 'MM') AS report_month,
    COUNT(account_id) AS number_of_accounts,
    SUM(deposit_amount) AS total_deposits
FROM accounts
GROUP BY customer_id, TRUNC(deposit_date, 'MM');

A report querying mv_monthly_customer_summary reads a stored result instead of re-running the join and aggregation each time, faster, at the cost of only being as current as its last refresh. Choosing between the two isn't a question of which is generally better; it's a question of whether a given report needs live, always-current numbers or can tolerate data that's current as of the last scheduled refresh in exchange for real speed. REFRESH COMPLETE ON DEMAND here means the materialized view only updates when explicitly told to, useful for a monthly report that genuinely only needs to reflect the world as of the last time someone actually asked for a refresh, rather than continuously.

This approach gives a database designer real flexibility down the line. If query performance would improve by physically pre-aggregating the data into a table rather than summing it fresh through a view every time, the view itself can be used to populate that table, and then redefined to read from it instead. This works precisely because nothing outside the database has ever depended on how customer_totals_vw computes its result, only on what it returns:

CREATE TABLE customer_totals AS
SELECT * FROM customer_totals_vw;

CREATE OR REPLACE VIEW customer_totals_vw
    (cust_id, cust_type_cd, cust_name, tot_active_accounts, tot_balance)
AS
SELECT cust_id, cust_type_cd, cust_name, tot_active_accounts, tot_balance
FROM customer_totals;

From this point forward, every query that references customer_totals_vw pulls from the new customer_totals table instead of recomputing the aggregation each time, and none of those queries need to change at all; they were never written against the base tables directly, only against the view's name. That's the entire point of building reporting on top of a view from the start: the view is the stable interface, and what sits behind it, a live aggregate query today, a precomputed table tomorrow, is free to change without disturbing anything depending on it.

Confirming the swap worked is straightforward, since the row count should match what the original view returned before the change:

SELECT COUNT(*) FROM customer_totals;

One thing worth being explicit about: customer_totals as built here is a genuine snapshot, not a materialized view. It has no refresh mechanism at all; the moment customer or account data changes, this table quietly falls out of sync with reality, and nothing about querying it produces any warning that it's gone stale, the exact same risk covered when this course first introduced the danger of saving a view's output into an ordinary table. If this data genuinely needs to stay current on some cadence, a materialized view with a defined REFRESH schedule, like the one shown above, is the properly managed version of this same idea; a bare CREATE TABLE AS SELECT with no refresh plan attached to it is not.

A handful of errors account for most of the trouble applying this pattern for the first time.

Forgetting that a hand-converted snapshot table has no refresh mechanism. As just covered, customer_totals doesn't update itself. Redefining customer_totals_vw to point at it is only safe once there's an actual plan, a scheduled job, a materialized view refresh, something, for keeping that table current; otherwise the "performance improvement" comes at the cost of silently serving stale numbers.

Assuming the column list in CREATE VIEW is just documentation. As demonstrated by the original mismatch between num_accounts and tot_active_accounts, an explicit column list actually renames the output columns, overriding whatever aliases the SELECT list used internally. Getting that list wrong doesn't cause an error; it just produces a view whose declared names don't accurately describe what it contains.

Choosing a materialized view without a real refresh need. Materialized views trade currency for speed on purpose. Reaching for one by default, rather than because a specific report has a genuine performance problem a regular view can't solve, adds refresh scheduling and storage overhead for no actual benefit over the view Oracle would otherwise merge and optimize automatically.

Looking Ahead

A few points from this lesson worth carrying forward:

• Reporting requests are almost always asking for aggregated figures rather than raw detail, and deciding where that aggregation actually happens is a real design choice, not an afterthought.
• A regular view built on GROUP BY and aggregate functions hides that logic from whoever queries it, the same abstraction principle covered throughout this course, applied here specifically to reporting.
• An explicit CREATE VIEW column list renames the output columns outright; it doesn't just document whatever aliases the SELECT list happened to use, and a mismatch between the two produces a view whose declared name misdescribes its own contents.
• Materialized views and plain CREATE TABLE AS SELECT snapshots look superficially similar but behave very differently: one has a real, managed refresh mechanism, the other has none at all and goes stale silently.
• A view built specifically to support reporting can have what sits behind it changed entirely, from a live query to a precomputed table to a materialized view, without a single downstream query needing to change, provided nothing was ever written against the base tables directly in the first place.