Variance and Growth in SQL

In this tutorial, I will show you how to find Variance and Growth in SQL

I am using SQL Server. Import Data into SQL Server. I will be using Sample Superstore Data.

I have created a Database named Superstore with Superstore data.

We now open a New Query Editor. Type the following command

USE Superstore
GO

The USE statement in SQL is used to specify the name of the database we want to select. We are selecting Superstore in this case.

Now, we write the query for Variance. Variance is nothing but the difference between Current Year Sales and Previous Year Sales.

SELECT DISTINCT
YEAR(dbo.Orders$.[Order Date]) as Years,
SUM([dbo].[Orders$].[Sales]) as Sales,
SUM([dbo].[Orders$].[Sales])-LAG(SUM([dbo].[Orders$].[Sales])) OVER (ORDER BY 
YEAR(dbo.Orders$.[Order Date])) as Variance
FROM
dbo.Orders$
group by 
YEAR(dbo.Orders$.[Order Date])
order by YEAR(dbo.Orders$.[Order Date]) asc

We are using LAG() because it refers to Previous Sales. Since it is a Window Function, we need to use the OVER clause. The output will be as shown below

Now, we write the query for Growth. Growth is nothing but the difference between Current Year Sales and Previous Year Sales divided by Previous Year Sales.

ROUND((SUM([dbo].[Orders$].[Sales])/LAG(SUM([dbo].[Orders$].[Sales]))
OVER (ORDER BY 
YEAR(dbo.Orders$.[Order Date]))-1)*100 ,2) AS Growth

Now after combining Variance and Growth into one query, we get the following

SELECT DISTINCT
YEAR(dbo.Orders$.[Order Date]) as Years,
SUM([dbo].[Orders$].[Sales]) as Sales,
SUM([dbo].[Orders$].[Sales])-LAG(SUM([dbo].[Orders$].[Sales])) OVER (ORDER BY 
YEAR(dbo.Orders$.[Order Date])) as Variance,
ROUND((SUM([dbo].[Orders$].[Sales])/LAG(SUM([dbo].[Orders$].[Sales]))
OVER (ORDER BY 
YEAR(dbo.Orders$.[Order Date]))-1)*100 ,2) AS Growth
FROM
dbo.Orders$
group by 
YEAR(dbo.Orders$.[Order Date])
order by YEAR(dbo.Orders$.[Order Date]) asc

Thank you for reading. Connect with me on Twitter and LinkedIn, and check out my Tableau Public profile for more visualizations.

Vignesh Suresh