You have written a correct query. Its result set is right. And yet the moment it lands in a spreadsheet, a column header reads SUM(e.salary), a blank cell appears where Research's city should be, and the rows come out in a different order each time someone refreshes. The numbers were never wrong; the presentation was never finished.
Exporting is the last mile, where a result set is shaped so a tool, a spreadsheet, a BI dashboard, a CSV file, can consume it without surprises. None of this changes the data. It changes whether the person, or program, on the other end trusts and reads it cleanly. The discipline is small: name columns in plain language, round numbers, replace NULLs, and sort deterministically. Do all four and the export just works; skip any one and a correct query ships looking unfinished.
flowchart LR
Q["SELECT shapes the result set"] --> T{"the tool writes the file"}
T -->|"sqlite3 CLI, Python csv, pandas"| F["CSV or spreadsheet"]
style Q fill:#0e7490,color:#fff
style F fill:#1e293b,color:#fff
Name and round every column
A raw SUM(e.salary) exports with that expression as its header, which is correct for the database and meaningless to a reader. Alias every computed column with AS payroll so the header is plain language before it ever reaches a file. Numbers need the same courtesy: an average salary of 633333.333333 is technically exact and practically noise. Wrap it in ROUND(AVG(salary), 0) and it snaps to a whole number, the precision a human actually wants.
The rule of thumb is to round to the precision the decision needs and no more. Money rounds to whole units or thousands, percentages to one or two decimals, counts stay whole. An alias fixes the header; ROUND fixes the value. Together they make a column read as if a person wrote it.
SELECT d.name AS department,
COUNT(*) AS headcount,
ROUND(AVG(e.salary), 0) AS avg_salary
FROM departments d
JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name;
NULL becomes an empty cell
This is the export trap that bites hardest. A NULL in your result becomes an empty field in a CSV, and empty fields are ambiguous: some spreadsheet tools read them as zero, some as blank, some as the text NULL, and none of them tell you which. A payroll total that is NULL because a department has no employees can show up as 0, silently indistinguishable from a real zero, or as a gap that breaks a chart.
The defence is to replace NULLs before they leave the query. COALESCE(SUM(e.salary), 0) turns a NULL total into a real 0; COALESCE(city, 'Unknown') turns a missing location into a readable label. The exported file then carries an honest value in every cell, one the downstream tool will read consistently. Coalesce anything that could be NULL and that a reader or a sum will touch.
flowchart LR N["NULL in the result"] --> E["empty CSV cell"] E --> Z["one tool sees 0"] E --> B["another sees blank"] C["COALESCE first"] --> S["a real value in every cell"] style N fill:#b45309,color:#fff style E fill:#b45309,color:#fff style C fill:#0e7490,color:#fff style S fill:#0e7490,color:#fff
SELECT d.name AS department,
COUNT(e.id) AS headcount,
COALESCE(SUM(e.salary), 0) AS payroll
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name;
Order once, export many times
Left to itself, a query returns rows in no guaranteed order. Run the same SELECT twice and the rows may arrive differently, because the engine's choice of order depends on things unrelated to your question. For a report on screen that is fine; for a CSV that someone diffs, or a file regenerated nightly, it is a defect. The second export looks nothing like the first even though the data is identical. Add an ORDER BY and the export becomes reproducible, the same rows in the same order every time.
The SELECT produces the result set, but a tool writes the actual file. The sqlite3 command line uses .mode csv and .headers on; a Python script uses csv.writer or pandas to_csv; a BI tool has its own export button. The SQL is the same in every case. Your job is to hand each of them clean, deterministic columns.
flowchart TD Q["same query, no ORDER BY"] --> A["run 1: one order"] Q --> B["run 2: a different order"] Q2["same query, ORDER BY name"] --> S["every run: the same order"] style B fill:#b45309,color:#fff style S fill:#0e7490,color:#fff
SELECT name AS department,
COALESCE(city, 'Unknown') AS city
FROM departments
ORDER BY name;
Format without losing the value
SQLite offers printf for tighter control, such as printf('%.2f', ratio) to force two decimals on a percentage, and strftime for dates, such as strftime('%Y-%m', hired_on) to render a month. These are useful when a column must display in an exact shape. The same caution from the callout below applies: use them to round or slice a value, not to decorate it into a string that can no longer be summed or sorted.
A percentage stored as a number with ROUND(ratio, 2) is still a number; the same value baked into the text 26.67% is not. Prefer ROUND for numbers, reach for printf when you genuinely need fixed formatting, and keep the type intact so the spreadsheet can still treat the cell as a value.
Shape a department report for export: each department's name as department, and its total project budget rounded to the nearest thousand as total_budget_k. Use ROUND(value, -3) for the rounding.
SELECT d.name AS department, -- total project budget rounded to thousands here AS total_budget_k FROM departments d JOIN projects p ON p.department_id = d.id -- add your GROUP BY here ;
The total is SUM(p.budget); wrap it in ROUND(..., -3).
Group by d.id, d.name so each department is one row.
SELECT d.name AS department, ROUND(SUM(p.budget), -3) AS total_budget_k FROM departments d JOIN projects p ON p.department_id = d.id GROUP BY d.id, d.name;
Build an export-ready department list that keeps every department, even one with no employees. Show department, headcount (count of employees, 0 if none), and payroll (SUM of salary, 0 if none). Use a LEFT JOIN and COALESCE so the export has no NULL cell.
SELECT d.name AS department, -- headcount and payroll, both coalesced to 0 FROM departments d -- LEFT JOIN employees, then group here ;
LEFT JOIN employees e ON e.department_id = d.id keeps departments with no staff.
COUNT(e.id) counts non-NULL employees; COALESCE(SUM(e.salary), 0) guards the total.
Group by d.id, d.name.
SELECT d.name AS department, COUNT(e.id) AS headcount, COALESCE(SUM(e.salary), 0) AS payroll FROM departments d LEFT JOIN employees e ON e.department_id = d.id GROUP BY d.id, d.name;
This department list is meant for CSV export but has two problems: city can be NULL, which becomes an ambiguous empty cell, and there is no ORDER BY, so each export lands in a different order. Fix it by replacing a missing city with 'Unknown' using COALESCE, and sort the rows by department name so the export is reproducible.
SELECT name AS department, city FROM departments ;
Wrap city in COALESCE(city, 'Unknown').
Add ORDER BY name so every export is in the same order.
Research should now show Unknown instead of a blank cell.
SELECT name AS department, COALESCE(city, 'Unknown') AS city FROM departments ORDER BY name;
Transfer skill: build a complete export-ready per-department summary. Columns: department, headcount, total_payroll (SUM of salary, coalesced to 0), and avg_salary_k (average salary rounded to the nearest thousand with ROUND(..., -3), coalesced to 0). Keep every department with a LEFT JOIN, leave no NULL cell, and order by total_payroll from highest to lowest so the file is reproducible.
SELECT d.name AS department, -- headcount, total_payroll, avg_salary_k FROM departments d -- LEFT JOIN employees, group, and order here ;
COUNT(e.id) for headcount; COALESCE(SUM(e.salary), 0) for total_payroll.
COALESCE(ROUND(AVG(e.salary), -3), 0) for avg_salary_k.
Finish with ORDER BY total_payroll DESC so the file is reproducible.
SELECT d.name AS department, COUNT(e.id) AS headcount, COALESCE(SUM(e.salary), 0) AS total_payroll, COALESCE(ROUND(AVG(e.salary), -3), 0) AS avg_salary_k FROM departments d LEFT JOIN employees e ON e.department_id = d.id GROUP BY d.id, d.name ORDER BY total_payroll DESC;
Why should a query destined for CSV export include ORDER BY?
A query with no ORDER BY returns rows in an arbitrary order that can change between runs. Ordering makes the export reproducible, so two runs over the same data produce identical files, which matters for diffs and nightly regenerations.
Recap
- Exporting shapes the result set so a tool can consume it cleanly; the SELECT shapes, the tool writes the file.
- Alias every computed column and
ROUNDnumbers to the precision a decision needs. - Replace NULLs with
COALESCEbefore export, because a NULL becomes an empty CSV cell that tools read inconsistently. - Add
ORDER BYso the export is reproducible, the same rows in the same order every time. - Keep numbers as numbers and dates in ISO order; let the spreadsheet do the pretty formatting, not the query.
That closes the loop from raw tables to a report a business can act on. This module carried you from choosing a grain to computing KPIs, reading them over time, guarding them with quality checks, parameterizing them for reuse, and now shipping them, finished.