Excel
Year Over Year Comparison From a Monthly Excel Sheet
Compare each month with the same month last year from an Excel sheet in SQL: LAG, self joins, partial years, missing months and percent change.
7 min read
Tag
comparison
58 posts
PostgreSQL
Seq Scan Versus Index Scan Versus Bitmap Scan Explained
Seq scan versus index scan versus bitmap scan in PostgreSQL: how each works, when the planner picks it, index only scans and why a seq scan can be right.
7 min read
JSON
MySQL JSON Versus MariaDB JSON and the Differences
MariaDB's JSON is validated text and MySQL's is a binary type. What differs in storage, operators, indexes, errors and speed, measured on 200,000 rows.
8 min read
Excel
Why Excel Says the File Format or Extension Is Not Valid
Excel says the file format or extension is not valid when the content does not match the extension. Identify the real format from its first bytes and fix it.
6 min read
Excel
Date Functions in Excel Versus SQL Date Functions Compared
Translate Excel date functions to SQL: YEAR, EOMONTH, EDATE, DATEDIF, NETWORKDAYS, WEEKDAY, WEEKNUM and TEXT, with worked results and week numbering traps.
7 min read
XML
Choosing Between XSD RELAX NG and Schematron for XML Checks
The same order checked three ways: XSD and RELAX NG passed a wrong total and a negative price, Schematron caught both, only RELAX NG took reordered children.
7 min read
Excel
Text Functions in Excel Versus SQL String Functions
Map Excel text functions to SQL string functions: LEFT, MID, LEN, TRIM, SUBSTITUTE, FIND, TEXTJOIN, PROPER and TEXT, with the edge cases for spaces and nulls.
7 min read
Excel
Removing Duplicates in Excel Versus SELECT DISTINCT
Reproduce Excel's Remove Duplicates in SQL: how DISTINCT and ROW_NUMBER choose the row to keep, how case and spaces matter, and how to delete duplicates.
7 min read
CSV
Loading a CSV Into MySQL Workbench Versus the Command Line
Use a GUI import wizard for small files and LOAD DATA for big ones. See the measured gap between row at a time and bulk loading, and what a GUI sends.
6 min read
Excel
Running Totals in Excel Versus Window Functions in SQL
Replace copied running-total formulas with SQL window functions: sums per group, moving averages, differences, ranks, and the tie trap in the default frame.
7 min read
Excel
INDEX and MATCH Versus a Join on a Key in SQL Compared
Translate INDEX and MATCH into SQL: one-way lookups, two-way table lookups, left-of-key lookups, match types and the join that replaces a lookup grid.
7 min read
Excel
Pivot Tables Versus GROUP BY and CASE in SQL Compared
Rebuild an Excel pivot table in SQL with GROUP BY, conditional aggregation, ROLLUP and window percentages, and see what changes when columns are fixed.
7 min read