Editorials

OVER()

I have been using aggregate functions for years. In all of that time I did not know how they may be used without a group by clause unless I was using one of the newer aggregate functions such as ROW_NUMBER(). However, you can include aggregate functions in your query without a group by clause if you use the OVER() clause with your aggregate function.

For example, you may write a query returning the cost for each item in a purchase order.

SELECT Amount

FROM PurchaseOrderItems

WHERE PurchaseOrderId = @PurchaseOrderId

What if you wanted to know, for each row, what the SUM of all the Amount items was for that purchase order, and include it on each row. You would use a query like the following:

SELECT Amount

,SUM(Amount) OVER() As TotalAmount

FROM PurchaseOrderItems

WHERE PurchaseOrderId = @PurchaseOrderId

You have applied the OVER clause to the SUM Aggregate function. There are no parameters for OVER(), so it will compute the sum of the Amount for all records returned in the query. This being the case, you could easily compute the percentage each item has of the overall total amount.
SELECT Amount

,SUM(Amount) OVER() As TotalAmount

,(Amount / (SUM(Amount) OVER()) as PercentageOfTotal

FROM PurchaseOrderItems

WHERE PurchaseOrderId = @PurchaseOrderId

Notice in all of these queries there is no GROUP BY clause. We get a separate record with the amount for each PurchaseOrderItem record. We get the sum of the Amount for the selected purchase order. And, we get the percentage each line item contributes to the total price.

Take a look at the OVER clause. It may make your life a lot simpler when working with aggregate values in your queries.

Cheers,

Ben

Facebooktwittergoogle_plusredditpinterestlinkedinmail
  • Mark Armstrong

    That only works because you have a WHERE clause on PurchaseOrderId so the OVER() is limited to a single Purchase Order. So it’s use in the real world is somewhat limited.

    Take the following example SQL from the Northwind Database

    select *, sum(unitprice) over() as TotalPrice, (UnitPrice / (sum(UnitPrice) over())) as PercentOfTotalPrice
    from Purchasing.PurchaseOrderDetail
    where PurchaseOrderID = 2

    You will get the expected values for TotalPrice and PercentOfTotalPrice because you have a where clause for a single Purchase Order.

    If you modify the query to include more than 1 purchase order then your aggregates are for the combined values which is useless.

    So you would (in my opinion) always use the PARTITION PARAMETER (to remove ambiguity) to ensure your aggregates are “grouped by” Purchase Order.

    If you want the correct values for each Purchase Order when you have more than one purchase order, your SQL would be:

    select *, sum(unitprice) over(partition by PurchaseOrderId) as TotalPrice, (UnitPrice / (sum(UnitPrice) over(partition by PurchaseOrderId))) as pct
    from Purchasing.PurchaseOrderDetail
    where PurchaseOrderID in (2,7)

    On a final note, you are missing a closed parenthesis on the following query:

    SELECT Amount
    ,SUM(Amount) OVER() As TotalAmount
    ,(Amount / (SUM(Amount) OVER()) as PercentageOfTotal
    FROM PurchaseOrderItems
    WHERE PurchaseOrderId = @PurchaseOrderId

    It should be:

    SELECT Amount
    ,SUM(Amount) OVER() As TotalAmount
    ,(Amount / (SUM(Amount) OVER())) as PercentageOfTotal
    FROM PurchaseOrderItems
    WHERE PurchaseOrderId = @PurchaseOrderId

  • Mattias Lind

    To simplify the answer from Mark Armstrong, OVER (PARTITION BY {your grouping set}) is giving you the result of the aggregate with a Group by, but inline with your not aggregated data. I dunno if that simplified, but anyways…