Another video brought to you by BeardedDev, bringing you tutorials on Business Intelligence, SQL Programming and Data Analysis.

You can now support me on patreon - [ Ссылка ]

In this SQL Tutorial we look at how we can use OVER to show totals of a query that involves GROUP BY. We start by explaining the issue, when using GROUP BY in a query we cannot show totals as we cannot perform an aggregate function on top of an aggregate function, this will cause SQL Server to display an error. How we can get round this is to call upon our friend Window Functions, by using the OVER clause we can then generate a total as this operates on the SELECT stage.

Please see the SQL Code used in this tutorial below:

SELECT
Sales_Customer_Id
, SUM(Sales_Amount) AS Cust_Total
, SUM(SUM(Sales_Amount)) OVER(ORDER BY (SELECT NULL)) AS Grand_Total
, AVG(SUM(Sales_Amount)) OVER(ORDER BY (SELECT NULL)) AS Average_Cust_Total
, CAST((SUM(Sales_Amount) / SUM(SUM(Sales_Amount)) OVER(ORDER BY (SELECT NULL))) * 100 AS DECIMAL(6,2)) AS Pct
FROM dbo.Sales
GROUP BY Sales_Customer_Id

свернуть