BA Prince the B.A. Subscribe

Home / SQL for Business Analysts

SQL for Business Analysts: The 5 Queries You Actually Need

SQL for Business Analysts: The 5 Queries You Actually Need

Watch on YouTube Subscribe

Five SQL queries cover most of what a business analyst needs: SELECT, WHERE, ORDER BY, GROUP BY and JOIN. Watch each one typed and run on a sample online shop database, with plain-English explanations.

Step by step

Five queries

  1. Five queries. With 5 SQL queries you can answer most business questions from data: SELECT, WHERE, ORDER BY, GROUP BY and JOIN. Each one is run below on a sample database for an online shop.
  2. The sample database. The database has 3 tables: customers, products and orders. Within a table, every row has the same columns.

SELECT

  1. SELECT. SELECT lists the columns you want, and FROM names the table they come from. This query gets the first_name and city of every customer. Run it. You get 40 rows, one for each customer, with only the 2 columns you asked for.
    SELECT first_name, city
FROM customers;

WHERE

  1. WHERE. WHERE keeps only the rows that meet a condition. To find cancelled orders, select order_id, order_date and status from orders and add WHERE status = 'Cancelled'. Put text values in single quotes. Run it. You get 11 rows, one for each cancelled order, from a 3-line query.
    SELECT order_id, order_date, status
FROM orders
WHERE status = 'Cancelled';

ORDER BY

  1. ORDER BY. ORDER BY sorts the result. ORDER BY order_total DESC puts the biggest orders first, and LIMIT 5 keeps only the top 5 rows. Run it. You get the 5 biggest orders, largest first.
    SELECT order_id, order_date, order_total
FROM orders
ORDER BY order_total DESC
LIMIT 5;
  2. The biggest order. Row 1 is the biggest order, with an order_total of 513. Top 5 lists like this show up in many reports.

GROUP BY

  1. GROUP BY. GROUP BY turns many rows into one row per group. COUNT(*) counts the rows in each group, and AS orders names that column orders. Run it. You get 4 rows, one per status. Most orders were delivered, and a few are still processing.
    SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status;

JOIN

  1. JOIN. The orders table has no region column, but customers does. JOIN links the 2 tables where c.customer_id matches o.customer_id. o and c are short names for orders and customers. SUM(o.order_total) adds up the sales. Next, add GROUP BY c.region so SUM gives one total per region, and ORDER BY sales DESC to put the biggest region first. One query now uses SELECT, JOIN, GROUP BY and ORDER BY together.
    SELECT c.region, SUM(o.order_total) AS sales
FROM orders AS o
JOIN customers AS c
  ON c.customer_id = o.customer_id
    
GROUP BY c.region
ORDER BY sales DESC;
  2. Sales per region. Run it. You get 4 rows: total sales for each region, biggest first.
  3. The top region. North is in row 1 and sold the most, by a wide margin. Managers often ask business analysts for sales by region.

Recap

  1. The five queries. Recap: SELECT picks columns, WHERE picks rows, ORDER BY sorts, GROUP BY makes counts and totals, and JOIN links tables. Practise these 5 and you can answer most everyday data questions yourself.

Common mistakes

  • Using double quotes around text. Write WHERE status = 'Cancelled' with single quotes. In standard SQL, double quotes mean a column name, so "Cancelled" can cause an error or return no rows.
  • Forgetting DESC on a top 5 list. ORDER BY sorts from smallest to largest by default, so without DESC, LIMIT 5 gives you the 5 smallest orders. LIMIT works in MySQL, PostgreSQL and SQLite, but SQL Server uses SELECT TOP 5 instead.
  • Selecting a column that is not in GROUP BY. In SELECT status, order_date, COUNT(*) ... GROUP BY status, most databases give an error, because order_date has many values per group. Either add the column to GROUP BY, or wrap it in a function such as MAX(order_date).
  • Leaving out the table name on a shared column. customer_id is in both orders and customers, so writing just customer_id in a JOIN gives an "ambiguous column" error. Put the short name in front, like c.customer_id or o.customer_id.

When a business analyst uses this

Before a workshop on order cancellations, a business analyst can run a GROUP BY on status to show how many orders are cancelled. They can then JOIN customers to see which regions those orders come from. The group then discusses real numbers instead of guesses, and the saved queries become evidence for the requirements.

Questions people ask

Do business analysts need to know SQL?

It depends on the role. Many business analyst jobs, especially ones that work with a lot of data, ask for basic SQL, while others don't. Even the basics let you check data yourself instead of waiting for a developer.

How should a business analyst start learning SQL?

Start with SELECT, WHERE, ORDER BY, GROUP BY and JOIN on a small sample database. Practise by answering real questions, such as how many orders were cancelled or which region sold the most.

What is SQL used for in business analysis?

SQL is the language for asking a database for data. Business analysts use it to pull lists, counts and totals, check data quality, and confirm what a system really stores before writing requirements.

More in SQL for Business Analysts

SQL SELECT Basics: Get Data Out of a Table (Beginner Tutorial) SQL WHERE Clause: Filter Rows Like an Analyst SQL INNER JOIN Explained With a Simple Example SQL LEFT JOIN: Find Customers With No Orders