Services

Technologies

Industries

About Us

Our Work - Case Studies

Top SQL Tips for 2023

Top 5 SQL Tips and Timesavers 2023

Top SQL Tips and Timesavers for 2023

Highlighting our top 5 SQL tips for 2023 based on popularity

 

1. Our most popular SQL tip this year

How to calculate running totals in SQL server

why you would use them, and provides a breakdown of the syntax

The article gives a step by step example of how to create a running total of daily sales, breaking down each aspect of the code

Running totals are an invaluable tool for data analysis, financial accounting, and forecasting. Helping to explain trends, performance and other changes over time

Running Totals in SQL Server

 

 

 

2. our second most popular SQL Tip for 2023

 

How to use the LEAD and LAG functions in SQL for analysing and optimizing data.

These functions are window functions that allow users to compare and analyse data points between rows in a specified window.

By using these functions, users can identify trends, correlations, and outliers in their data sets more easily than ever before

 

The post explains that the LEAD function is used to access subsequent rows along with data from the current row, while the LAG function is used to access previous rows along with data from the current row.

These functions are especially useful when analysing time-series data that is stored in SQL databases. Its one of those things that is easy to do in Excel, but needs a bit of knowledge in SQL

 

LEAD and LAG Functions in SQL

 

3.  In Third place

How to use the ROLLUP extension of the GROUP BY clause in SQL Server to generate subtotals and grand totals for multiple dimensions within a dataset.

The post provides a simple example to illustrate the steps for using the ROLLUP function in SQL.

Assuming you have a table of daily sales records, you can use the ROLLUP function to insert sub-total rows for each year, quarter, and month name

The ROLLUP function is a powerful yet simple way to add subtotals to your queries. And the flexibility to use partial rollups allows you more control over when to use them.

The feature can be used for any hierarchical data set to instantly add useful subtotals without the need to write separate subqueries or use window functions

Group by ROLLUP in SQL Server

 

4. The Fourth most popular

How to avoid the common error of dividing by zero in SQL.

We provide a simple logic to make sure the division by zero errors do not get in the way of analysis.

The post explains that dividing by zero makes no sense logically, as the result cannot be determined

The post provides a range of solutions to avoid these issues with simple logic.

The solutions include checking the denominator first, using a safe division type of option which has built-in divide by zero handling, and using the NULLIF command around the denominator value

Although this is one of the most popular SQL posts this year, it also cover Excel, Power Query, Power BI (DAX) and Google BigQuery

 

 

Divide by Zero Errors

 

 

 

5. And concluding our top 5 most popular SQL tips for 2023

 

A step-by-step guide on how to split a column in SQL Server.

The guide shows how to split one column into multiple columns in SQL Server. This can be a very common problem, but with a few commands, it is easily achieved.

The post provides an example of splitting a column containing full names into two columns, one with the first name and one with the last name. The post explains how to find the position of the delimiter, how to find the length of the full name. Then how to find the characters that make up the first name, and how to find the last name

How to split a column in SQL Server

 

 

Subscribe to our channel to see more tips and timesavers

Select Distinct YouTube Channel

Or find other useful SQL, Power BI or other business analytics timesavers in our Blog

Our Business Analytics Timesavers are selected from our day to day analytics consultancy work.  They are the everyday things we see that really help analysts, SQL developers, BI Developers and many more people.

Our blog has something for everyone, from tips for improving your SQL skills to posts about BI tools and techniques. We hope that you find these helpful!

Business Analytics Blog

By Simon Harrison

Simon Harrison Founder of Select Distinct Limited and a business intelligence expert