The system fails because it's using the wrong tool for the job. Vector search finds text that is approximately similar to the question. For 'what does this clause mean?' that's perfect. For 'what's the total revenue across all segments?' it's the wrong primitive.
Two things break at once. First, similarity search returns the chunks that look most related, not a guaranteed-complete set of every figure you need. Some numbers simply aren't retrieved. Second, even if they were all present, the model is being asked to add them up in its head across separate chunks, and that arithmetic is unreliable.
The right design is to detect that the question is numeric or aggregate, then route it to structured retrieval. Instead of feeding chunks to the model, you turn the question into a precise query — text to SQL, or extracting the relevant table — and let the data store compute the exact value.
More chunks or smaller chunks don't fix this. An exact total needs an exact query, not approximate similarity plus mental math.
The correct answer is to route numeric and aggregate questions to structured retrieval — text to SQL or table extraction. The values are then computed exactly, rather than asking the LLM to add up scattered chunks.
The failure has two independent causes. Vector retrieval returns text that is approximately similar to the query, not the complete, exact set of rows an aggregate requires. A sum over 'all segments' needs every segment's figure; similarity search has no guarantee it surfaced all of them. On top of that, even with the figures present, the model is doing arithmetic over numbers spread across separately retrieved chunks, and LLMs are unreliable at multi-step arithmetic. So you get confidently wrong totals.
The other options treat symptoms. Raising top-k just floods the context with more chunks — more noise, no guarantee of completeness, and the model is still doing the math. Smaller chunks improve embedding precision but don't make aggregation exact. A bigger financial-text embedding model improves semantic matching, not the model's arithmetic.
The principle: exact aggregates require an exact query. Recognize numeric intent, generate a query against the structured source, execute it deterministically, and let the LLM phrase the result. Similarity search is for finding relevant text, not for computing answers over known records.
Quick reference
TL;DR
Similarity search returns approximately relevant text, not the exact rows an aggregate needs; route numeric and aggregate questions to structured retrieval like text to SQL so totals are computed exactly.
Imagine asking a helper to total your monthly spending, but instead of handing them your bank statement, you hand them a stack of sticky notes that merely look related to money. They'll add up whatever notes they happened to grab and confidently give you a number — but it'll be wrong, because some receipts were never in the stack and they're doing the math in their head. The fix isn't a thicker stack of sticky notes or fancier handwriting. The fix is to pull up the actual statement in a spreadsheet and let the calculator add the column. For a system reading financial reports, that means turning 'what's the total revenue?' into a precise database query that runs over the real rows, instead of letting the language model eyeball a pile of similar-looking text.
Everything you need to truly understand this topic: intuition, mechanics, step by step explanation, code, formulas, and worked example. Click to expand.
Everything you need to truly understand this topic: intuition, mechanics, step by step explanation, code, formulas, and worked example.
The source is unstructured prose with no tables behind it There's no database to query; you must first extract figures into a structured form before text to SQL applies.
Single-figure lookups where one number sits in one passage If no aggregation is needed, ordinary retrieval returns the figure directly and a SQL layer is overkill.
Schema is too messy or ambiguous for reliable text to SQL generation Poor schema linking yields wrong but plausible queries; invest in schema cleanup or curated views before routing here.
Strict read-only or compliance constraints without query guardrails in place Executing generated SQL against live data without role limits and validation risks data exposure or unsafe queries.
Quick scan · 2–4 min
Everything important, quickly.
3-4 min: why similarity search can't guarantee a complete figure set, why LLM arithmetic over chunks is unreliable, and the route to structured retrieval (text to SQL / table extraction) fix versus the distractors.
Side-by-side
Question type
Right retrieval approach
Exact figures or aggregates (sum, count, max, compare)
Structured query: text to SQL or table extraction, executed for an exact result
Fuzzy or semantic lookup over prose
Vector retrieval over chunks
Mixed (narrative plus a number)
Route per sub-question, then combine the results
Real-world examples
Real products, models, and research that use this idea.
1A finance assistant over 10-K filings that quoted wrong segment totals until aggregate questions were routed to text to SQL over an extracted tables database.
2LlamaIndex and LangChain ship SQL query engines and table-retrieval tools precisely for this numeric-question path alongside vector retrieval.
3BI copilots (e.g., over a warehouse) that generate and run SQL for 'total', 'average', and 'compare' questions rather than retrieving prose summaries.
Interviewer follow-ups
What an interviewer would ask next. Try answering before peeking at the approach.
QHow does the router decide a question is numeric/aggregate versus a factual lookup?
Sign in or go Pro to see the approach for this follow-up.
QWhat are the main failure modes of text to SQL and how do you mitigate them?
Sign in or go Pro to see the approach for this follow-up.
QWhen figures live in unstructured PDFs, what has to happen before structured retrieval works?
Sign in or go Pro to see the approach for this follow-up.
Don't say this
Red flags and common mistakes that signal junior thinking. Click to expand.
Most common mistake
Trying to fix wrong totals by raising top-k or shrinking chunks; both keep the LLM doing unreliable mental math over an incomplete set of figures.