Excel to SQL cheat sheet
Every spreadsheet move on this site and the SQL that does the same job. Function names follow SQLite, which runs the lessons in your browser.
| In Excel | In SQL | |
|---|---|---|
| Look at a sheet | SELECT * FROM table | Lesson |
| Pick some columns | SELECT name, country FROM customers | Lesson |
| Filter | WHERE country = 'Germany' | Lesson |
| Sort A to Z / Z to A | ORDER BY col / ORDER BY col DESC | Lesson |
| Top 10 rows | LIMIT 10 | Lesson |
| =SUM / =COUNT / =AVERAGE | SUM(col) / COUNT(*) / AVG(col) | Lesson |
| =MIN / =MAX | MIN(col) / MAX(col) | Lesson |
| Count unique values | COUNT(DISTINCT col) | Lesson |
| =COUNTIF / =SUMIF | WHERE … then COUNT(*) / SUM(col) | Lesson |
| Several COUNTIFS side by side | SUM(CASE WHEN … THEN 1 ELSE 0 END) | Lesson |
| Pivot table | GROUP BY col | Lesson |
| Pivot value filter | HAVING SUM(col) > 100 | Lesson |
| =VLOOKUP / =XLOOKUP | JOIN other ON other.id = t.other_id | Lesson |
| VLOOKUP keeping #N/A rows | LEFT JOIN | Lesson |
| =IF / =IFS | CASE WHEN … THEN … ELSE … END | Lesson |
| Remove Duplicates / =UNIQUE | SELECT DISTINCT col | Lesson |
| =LEFT / =MID | substr(text, start, length) | Lesson |
| =LEN | length(text) | Lesson |
| =A2&" "&B2 | a || ' ' || b | Lesson |
| =SUBSTITUTE | replace(text, 'old', 'new') | Lesson |
| =FIND | instr(text, 'x') | Lesson |
| =TEXT(date,"yyyy-mm") | strftime('%Y-%m', date) | Lesson |
| =B2-A2 (days between) | julianday(b) - julianday(a) | Lesson |
| =EDATE(date, 1) | date(d, '+1 month') | Lesson |
| Moving average dragged down | AVG(x) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) | Lesson |
| Running total | SUM(x) OVER (ORDER BY d) | Lesson |
| =RANK within a group | ROW_NUMBER() OVER (PARTITION BY g ORDER BY x DESC) | Lesson |
| A helper sheet | WITH helper AS (SELECT …) | Lesson |