Generating accurate subtotals on fanout joins

Question

My data model:

The query:

SELECT
    ProductSummary.Product,
    ProductSummary.ID AS SummaryID,
    Transactions.DateOfSale,
    Summary.Revenue
FROM
    ProductSummary JOIN
    Transactions ON (Transactions.ProductID = ProductSummary.ID)
WHERE
    Transactions.DateOfSale < '2014-01-10'

The data itself looks fine, however I also want to show a subtotal, and the subtotal of a table should be the amount displayed when that table is not joined.

For example, for subtotaling Revenue the answer should always be what I would get from SELECT SUM(Revenue) FROM Summary (after applying any necessary filters). How to generate that?

Answer 1

One way to do this would be using an analytic function to count the unique rows while totaling, for example:

WITH
    ProductSummary (Product, ID, Revenue) AS (
        SELECT 'Car', 1, 12 UNION ALL
        SELECT 'Phone', 2, 7
    ),
    Transactions (SummaryID, ID, DateOfSale) AS (
        SELECT 1, 1, DATE '2014-01-01' UNION ALL
        SELECT 1, 2, DATE '2014-01-02' UNION ALL
        SELECT 1, 3, DATE '2014-01-03' UNION ALL
        SELECT 2, 4, DATE '2014-01-04' UNION ALL
        SELECT 1, 5, DATE '2014-01-04' UNION ALL
        SELECT 1, 6, DATE '2014-01-04' UNION ALL
        SELECT 1, 7, DATE '2020-01-01'
    )
SELECT
    ProductSummary.Product,
    ProductSummary.ID AS SummaryID,
    Transactions.DateOfSale,
    ProductSummary.Revenue,
    IF(
        ROW_NUMBER() OVER (PARTITION BY ProductSummary.ID) = 1,
        ProductSummary.Revenue,
        0
    ) RevenueUnique
FROM
    ProductSummary 
    JOIN Transactions ON (Transactions.SummaryID=ProductSummary.ID)
WHERE
    Transactions.DateOfSale < DATE '2014-01-10';