SQL Recipies
Latest Posts
-
Calculate a Running Total in SQL using a Windows Function
Problem How do you create a running total on a column using SQL? Background There is no built-in function to create a running total, but you can use a windows function to do so. In this case well construction a running total of Weekly Product Sales. For this solution we’ll work with the WinCode WeeklySales…
-
How to Round Up to Nearest Integer
Problem You want to round up to the nearest integer. Background Suppose you have a list of final sales amount that you want to round to make your sales analysis easier. For this example, we’ll use data from the PizzaDB sample database. Here is the query you can use with MS SQL, PostgeSQL, and MySQL…
-
Use SQL to Find the MEDIAN
Problem You need use SQL to calculate the Median of a result from SQL Server. Background Suppose you need to calculate the Median using SQL. You can loosely define the median as the “middle” value of your data set. If you were calculating the median by hand, you would use the following rules to do…
-
How to Avoid SQL Divide by Zero
Problem You need to avoid SQL divide by zero errors. Suppose you need to calculate velocity using the formula v = s / t Where v = velocity, s = distance, and t = time. Here is the sample data we’ll use. select 100 s, 10 t union all select 110 s, 11 t union all select 2 s, 0 t union all select 10 s, 1 t union all select 120 s, 20 t Line Three is going to cause us issues,…
-
How to Subtract 30 Days from a Date using SQL
Problem Given a date, you want to subtract 30 days in the past using SQL. Said another way, get the date thirty days in the past. We’ll show you to do this using MySQL, PostgreSQL, and SQL Server. Each uses a different approach, so be sure to read each solution to gain an appreciation of…
-
How to Find the Week Number in PostgreSQL, MySQL, and SQL Server
Problem You need to figure out the week number from date, assuming Monday is the first day of the week using PostgreSQL, MySQL, or SQL Server. Background To work through this problem, we’ll use orders for smelt pizza. For each order date we’ll calculate the week number in PostgreSQL. Our example is based on the…
-
Replace using PostgreSQL
Problem You want to replace all occurrences of a substring with a new substring using PostgreSQL, MySQL, or SQL Server. Background The owners of the PizzaDB shops are changing their drinks from Coke to Pepsi. Let’s help them see what the changes would be, before they make them permanent. We’ll write a query show them…
-
How to Get Today’s Date
Problem You want to use today’s date in your SQL query, but don’t want to type it in each day. Solution You can use GETDATE() to return the current date and time. But to remove the time part case the result as a DATE. Assuming the current date is 2022-02-27, then SELECT CAST(GETDATE() as DATE)…
-
How to Get a Date 30 Days into the Future using SQL
How to Get a Date 30 Days into the Future. Problem You want to calculate a date 30 days into the Future using SQL. We’ll use the AdventureWorks Production.Product table for our example: select ProductID, Name, SellStartDate from Production.Product where productID in(1, 770, 927) Let’s add thirty days to select Product SellStartDates. Solution Use the SQLSERVER DATEADD() function to add 30 days to…
-
Select the Top Row from your result.
Select Top Rows In this video we’ll walk you though how to select top rows within a query. We’ll show you how to retrieve the top or bottom rows in the result. We’ll also show you how to use one sort to select the rows, and once completed, another sort to present them (tricky).…