SQL for spreadsheet users

Who this is for

It is for people who use filters, SUMIF and pivot tables at work and want to start asking questions of a database. You do not need to be a programmer. You do need someone to give you read access to a database, or you can practise on a free local tool.

The plan at a glance

1. 1.Think of a table as a sheet with a header row.
2. 2.Use SELECT and FROM to choose columns.
3. 3.Use WHERE to keep only the rows you want.
4. 4.Use GROUP BY with SUM or COUNT to summarize.

The mapping from spreadsheets

Worked example: a filter and a SUMIF in SQL

Imagine a table called orders with five rows. In a sheet it would look like this.

Choosing columns and filtering

In a sheet you would filter the region column to North and look at customer and amount. In SQL:

SELECT customer, amount
FROM orders
WHERE region = 'North';

The result is three rows: Ana 120, Cara 50, Eli 100. The text 'North' goes in single quotes. Read the query as a sentence: select these columns from this table where the region is North.

The SQL version of a SUMIF

The spreadsheet formula =SUMIF(C2:C6, "North", D2:D6) adds the amounts where the region is North. The SQL version:

SELECT SUM(amount)
FROM orders
WHERE region = 'North';

Check by hand: 120 + 50 + 100 = 270. Both the formula and the query should return 270.

GROUP BY: every region at once

A pivot table would put region in Rows and the sum of amount in Values. In SQL, GROUP BY does the same:

SELECT region, SUM(amount)
FROM orders
GROUP BY region;

The answer is North 270 and South 280. South is 80 + 200 = 280. The two groups add up to 270 + 280 = 550, which equals the sum of all five amounts: 120 + 80 + 50 + 200 + 100 = 550. That is the same check you would use on a pivot table.

You can combine them. Adding WHERE amount > 90 before GROUP BY keeps only orders over 90, which are 120, 200 and 100. Then North is 120 + 100 = 220 and South is 200. Order matters: WHERE filters rows first, then GROUP BY summarizes what is left.

Habits that save time

Write queries in small steps. Start with SELECT * FROM orders to see the table, add a WHERE, run it, then add the grouping. If something breaks, you know which step caused it.

Put each clause on its own line, as in the examples. It makes queries easier to read and to fix. Name things clearly, and keep a short note of what each query is for, so you can reuse it next month.

Finally, remember a database does not show you the whole table the way a sheet does. If you forget a WHERE, a query may return a huge number of rows. Ask for a limited preview first, using the limit syntax your database supports, before you run something large.

One more difference from a sheet: rows in a table have no built-in order. If you want the biggest orders first, say so with ORDER BY amount DESC. Without it, the database may return rows in any order, and the order can change between runs.

If something goes wrong

• Syntax error. Look for a missing comma between columns, a missing quote, or a trailing comma before FROM.
• Unknown column. The column name is spelled differently in the table. List the table's columns first.
• The numbers look wrong. Run the query on a small slice and add it up by hand, as above.
• Another column in SELECT breaks GROUP BY. Every column in SELECT must be in GROUP BY or inside a function like SUM.

Keep it going

Next learn ORDER BY, COUNT, and JOIN, which is like a lookup between two sheets. Keep a file of your working queries, the way you would keep a good formula. If you want help reading a query on your screen, DuckyHelper can explain it out loud while you keep working.