The errors that actually show up
When a query looks correct but the numbers are wrong, the cause is usually one of five things: a join that duplicates rows, a NULL that drops a group, a column referenced from the wrong table, an aggregation that double-counts, or a filter that excludes more than you meant. None of these throws an error, which is exactly why they are dangerous. Build the habit of checking the shape of the result, not just the absence of a red message.
Run
SELECT COUNT(*)
FROM (your query) qand compare to what you expect. If a monthly report returns 900,000 rows when Orders has 12,000, you have a join explosion and the total is meaningless.
Pick one region you can compute by hand.
SELECT SUM(Amount)
FROM Sales
WHERE Region = 'West'should match the West line in your report. If it does not, the query is wrong even though it ran clean.
I recommend keeping a small hand-counted check row for every report, like total revenue for one month. A common mistake is shipping the query because it returned a number, then finding a week later that the number was double the truth.
Catching real syntax errors
Syntax errors do throw a message, but the message often points at the wrong line. The engine reports where it got confused, which is usually just after the real mistake. Train yourself to read the query from the top and look for the missing comma, unclosed quote, or missing GROUP BY before the reported line.
SELECT CustomerID Amount FROM Orders misses the comma between columns. The error mentions Amount, but the fix is the comma before it. Scan the column list for two names touching with no comma between.
SELECT Region, SUM(Amount)
FROM Sales throws if Region is not groupedAdd GROUP BY Region. A common mistake is selecting a bare column next to an aggregate and forgetting to group by it, which some engines reject and others fill with a random row.
Count opening and closing parens in a WHERE clause with a subquery. This will break with a vague syntax error if they do not match. Rewrite the subquery on its own line so each pair is visible before pasting it back.
A common mistake is trusting the line number in the error. The engine fails where it first cannot parse ahead, which can be several tokens after the real typo. Read upward from the reported line, not downward.
Logic errors that return wrong numbers
Logic errors are the silent ones. The query runs, the columns look right, and the total is wrong. They come from how you joined, grouped, or filtered, not from the grammar. These are the errors that cost analysts their credibility, so they deserve the most debugging time.
SELECT o.OrderID, c.Name
FROM Orders o
JOIN Customers c
ON o.CustID = c.CustomerIDIf Orders uses CustID but you join on CustomerID, unmatched rows either vanish or multiply. Check the actual column names in both tables before trusting the join.
Joining Orders to a Customers table that has duplicate CustomerID rows multiplies each order. Run
SELECT CustomerID, COUNT(*)
FROM Customers
GROUP BY CustomerID
HAVING COUNT(*) > 1to find duplicates before you join.
SUM(Amount) without GROUP BY returns one row for the whole table, which is wrong if you wanted per region. Add GROUP BY Region and confirm the row count equals the number of regions, not one.
I recommend testing joins on a tiny sample, for example WHERE OrderDate = '2026-03-01', so a row explosion is 50 rows instead of 900,000 and you can see the duplicate pattern immediately.
NULL handling that silently breaks totals
NULL is not a value; it is the absence of a value, and it behaves strangely in comparisons and groups. A NULL in a join key drops the row from an inner join. A NULL in a column you GROUP BY creates a mystery group. A NULL inside NOT IN can make the whole filter return nothing. Treat NULL as a first-class suspect in every wrong-number bug.
SELECT COUNT(*)
FROM Orders
WHERE CustomerID IS NULLIf any row is NULL, an inner join drops it and your revenue undercounts. Decide whether to fix the source data or keep those rows with a LEFT JOIN plus COALESCE.
WHERE CustomerID NOT IN (SELECT CustomerID FROM Vip) returns zero rows if Vip has a single NULL CustomerID. This will break your exclusion filter silently. Use NOT EXISTS instead, which handles NULLs correctly.
AVG(Amount) ignores NULLs, but SUM of a column with NULLs can still surprise you. Use COALESCE(Amount, 0) when a missing value should count as zero, and state that choice in a comment so a teammate understands it.
Join explosions and how to spot them
A join explosion happens when one row on the left matches many rows on the right, so the result has far more rows than either table. The totals then look plausible until someone notices revenue is ten times too high. The cure is to know the cardinality of both sides before you join.
SELECT COUNT(*)
FROM Orders returns 12,000;SELECT COUNT(DISTINCT CustomerID)
FROM Ordersreturns 4,000. If your join yields 900,000 rows, the right table has about 75 copies per customer, which is the explosion.
If Customers links to many Address rows, pre-aggregate
WITH OneAddr AS (
SELECT CustomerID, MAX(City) AS City
FROM Addresses
GROUP BY CustomerID)then join Orders to OneAddr. One row per customer keeps the join clean.
When you only need existence, use WHERE EXISTS (
SELECT 1
FROM Addresses aWHERE a.CustomerID = o.CustomerID) instead of a JOIN that multiplies rows. EXISTS stops at the first match and never duplicates the order.
A debugging toolkit you can reuse
Debugging is faster with a fixed routine than with random edits. The routine below works on every report: shrink the data, check counts, read the plan, isolate the bad step. Follow it the same way each time and you will find bugs in minutes instead of hours.
Add WHERE OrderDate = '2026-03-01' to the whole query. A bug that produced 900,000 rows now produces 50, and you can read every one to see the duplication pattern.
EXPLAIN SELECT ... FROM Orders o JOIN Customers c ON ... shows whether the engine expects a few rows or millions. A row estimate far above reality means a bad join or missing index driving the wrong path.
Run the innermost SELECT alone and check its count, then the next layer, then the final. A common mistake is debugging the final SELECT when the error is three steps earlier in a CTE you never ran on its own.
A common mistake is editing the query while guessing. Change one thing, rerun, read the count, then change the next. Random edits hide which fix actually worked and can introduce a second bug on top of the first.
Worked example: revenue ten times too high
A monthly revenue report returned $4.8 million for March when finance expected $480,000, exactly ten times too high. No error appeared. Here is how we traced it to a join explosion in a few minutes.
SELECT COUNT(*)
FROM (
SELECT o.OrderID,
c.Name
FROM Orders o
JOIN Customers c
ON o.CustomerID = c.CustomerID
WHERE o.OrderDate >= '2026-03-01') qThe count was 120,000, but Orders for March held only 12,000 rows. Ten copies per order pointed straight at the join.
SELECT CustomerID, COUNT(*)
FROM Customers
GROUP BY CustomerID
HAVING COUNT(*) > 1returned 11,000 customers with up to 11 rows each. A bad import had loaded Customers many times, so the join multiplied every order.
Deduplicate Customers to one row per CustomerID, rerun the report, and the March total dropped to $482,000, within 0.4 percent of finance's figure. The query was correct all along; the data was not.
Preventing errors before they ship
The cheapest bug is the one that never reaches a dashboard. A few habits at write time remove most silent errors: constrain your keys, check counts on every new join, and keep a hand-computed check value. None take long, and together they catch the mistakes that otherwise surface in a meeting.
ALTER TABLE Customers ADD UNIQUE (CustomerID). The database then rejects the duplicate import that caused the explosion, so the bad load fails loudly instead of quietly multiplying rows.
Write the expected count next to the query, for example March Orders = 12,000. A teammate who sees 120,000 later knows immediately something regressed without recomputing from scratch.
Write FROM Orders o JOIN Customers c ON ... instead of FROM Orders o, Customers c WHERE .... Explicit joins make the relationship visible and stop the accidental cross join that produces every-against-everything.
Mistakes that waste the most debugging time
Some habits make bugs harder to find. Debugging the final SELECT instead of the innermost step, trusting the error line number, and shipping a number with no check row all stretch a ten-minute fix into an afternoon. Name them so you stop doing them.
A wrong total almost always comes from an early step. This will break your timeline if you only tweak the final SELECT. Start at the innermost SELECT and walk outward, confirming counts at each layer.
A common mistake is not noticing a NULL group in the output.
SELECT Region, SUM(Amount) can include a blank Region row from NULL values;
Decide whether those rows belong in the total or should be excluded explicitly.
Before any join, verify the right table has one row per key. Skipping this check is the single most common cause of the ten-times-too-high report that embarrasses a team in front of finance.
Next steps to build the habit
Adopt the same debugging routine on your next report: shrink the data, check the row count, read EXPLAIN, then isolate each step from the inside out. Keep one hand-computed check value per report and write the expected count in a comment. Once that is muscle memory, pair it with clean joins and sound NULL handling so the bugs rarely appear in the first place. Review your slowest query this week with this lens and you will likely find a silent error hiding in plain sight.
