In this article let’s look at window functions vs GROUP BY queries.
Writing a SQL Query is part art and part craft. I say this as not only do you need to know a query’s syntax and technical considerations, the craft, but you also need to understand how to formulate a query to match real-world use cases, the art.
For if you can take a customer’s request, or yours for that matter, and formulate it into a meaningful SQL statement, not matter how well you understand how a SQL query works, you won’t deliver value.
SQL Queries – Craft
Before we get into the art, let’s explore the craft. I’ve already written a slew of articles to help you. Here are some to get you started writing SQL Queries:
- SQL SELECT
- SQL WHERE – Guide and Examples
- Use SQL ORDER BY to Sort Results
- SQL GROUP BY Guide
- SQL Joins – The Ultimate Guide
- Ultimate Guide to Subqueries
You may already know some of these topics. That’s great! If not, just start with SQL SELECT. It’s a great starting point.
The ART of Writing SQL QUERIES
To be honest, I think this is the hardest part to master. Here you need to learn to “encode” your customers requirements into SQL to get the desired result. It’s frustrating, as SQL is one of those language where you don’t tell the computer “how” to do it, but “what” you want.
For some developers this is a big change, for they are used to writing algorithms to instruct the computer how to do operations.
With SQL you specify what result you want returned, and then it is up to the DBMS (Database Management System) to make it happen.
I’ve tried to convey the process I used to write a query in the Three Steps to Writing SQL. You’ll see I go through all the steps in detail. Once you get the hang, these steps become intuitive. It becomes second nature before long.
In this article I’ll show you how to use STRING_SPLIT() to join two tables. I call this the impossible join. I’ve come
Sooner or later, you want to know when a column contains in SQL another value. In this article we’ll go over several
Use SQL HAVING to filter summary results from a GROUP BY. It specifies the search condition for the group or aggregate. SQL
Use the SQL GROUP BY Clause is to consolidate like values into a single row. The group is a set of columns. The group
In this article learn when to use WHERE and HAVING. Both perform similar functions, but for different purposes! All the examples for
In this article you’ll learn to use the LIKE operator in your SQL. In most situations you’ll find yourself using LIKE within
In this video we’ll walk you though how to use the LIKE clause. We’ll show you how to you can perform
In today’s lesson, you’re going to learn how to filter query results using the SQL WHERE clause. This clause is important as
The SQL ORDER BY clause is used to sort your query result in ascending or descending order. Once you have written a
Both TOP and OFFSET & FETCH can be used to limit the number of rows returned. OFFSET and FETCH can return similar
In this article we explore the OFFSET and FETCH clauses. OFFSET and FETCH are used in conjunction with the SELECT statement ORDER
Some times you just need to use SQL TOP to reduce the number of rows shown in your result. This is handy
You will learn how to query Microsoft SQL Server using the SQL SELECT statement in this series of lessons. Once you have
Its is easy to get confused between inner join vs outer join clauses. Both inner and outer joins are used to combine
The SQL FULL JOIN combines results from a left and right outer join into one result. Or in other words, it is an inner join including
The SQL RIGHT JOIN Includes all rows from the Right table and those that match from the left. For that doesn’t match from the right, NULL is returned in the
The SQL LEFT JOIN Includes all rows from the left table and those that match from the right table. When the right table doesn’t match the
SQL Joins are important to master. As you progress from a beginner to advanced beginner, you’ll soon need to combine data from
Use SQL cross joins when you wish to create a combination of every row from two tables. All row combinations are included
Use an SQL INNER JOIN when you need to match rows from two tables. Rows that match remain in the result, those
In a SQL self join the table is joined to itself. You can think of it being like an inner join. The
In this puzzle, we’re going to learn how to calculate moving averages by working through a hypothetical stock market example. Solving puzzles
Recursive CTE’s Recursive CTEs are unique, such that they are allowed to reference their own. With this special ability, you can use
The Common Table Expressions or CTE’s for short are used within SQL Server to simplify complex joins and subqueries, and to provide
A reader recently asked me to help them solve a problem combining data from multiple tables into a single result. What was
