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