LLMs Make Aggregation Errors: Why SUM, AVG, and COUNT Go Wrong

SUM, AVG and COUNT answer for whichever rows reach them, so a ratio or a distinct count that is averaged or added a second time answers a different question. Google's developer team says that adding up daily session counts "would be double counting the sessions that span across multiple days", and the same logic makes an average of campaign ROAS values differ from total revenue over total spend. The query still runs; the total belongs to another question.

What do the studies and documentation show?

Three sources speak to aggregation: two on model-written SQL, one on GA4 itself.

• Shen et al. (peer-reviewed, FSE 2026) read 4602 wrong queries from four text-to-SQL systems on the BIRD and Spider development sets, run with GPT-3.5 and GPT-4o. Semantic errors were 36.1% of all errors on BIRD and 17.7% on Spider. Around 40% of the semantic errors were projection errors, and "the generated queries frequently use a wrong numerator or denominator in a division operation, especially for ratio calculations." The taxonomy also names a COUNT aimed at the wrong column, for example "COUNT(*) instead of counting the unique molecule IDs".
• Spider 2.0 (peer-reviewed, ICLR 2025 Oral) put advanced data calculation, such as quantile grouping and statistical formulas, at 7.5% of the errors in its review of 300 randomly sampled examples. The benchmark includes Google Analytics tasks such as "the number of sessions and percentage breakdown by channel for December 2020".
• Google for Developers (official guidance, 2023, page updated 2026) is documentation, not a measurement. It says GA4's interface estimates Active Users and Sessions with the HyperLogLog++ algorithm, so they are "an approximation with a certain precision", and says the session count's precision "might be ±1.63%" at a 95% confidence interval.

The two studies use public benchmarks and 2024-era models, and the figures above are shares of errors, not failure rates per query. No study cited here measures how often an LLM-written query mis-aggregates Shopify or GA4 data.

Why is an average of ratios, or a sum of distinct counts, wrong?

A ratio is a pair of totals, and a distinct count is a set. Averaging the first or adding up the second treats a result as if it were raw data.

For illustration: campaign A spends 100 and brings 400 (ROAS 4.0) and campaign B spends 900 and brings 900 (ROAS 1.0); the average of the two ROAS values is 2.5, but total revenue over total spend is 1,300 / 1,000 = 1.3.

Grain matters the same way. For illustration: take two orders, one with a single line worth 100 and one with three lines worth 10 each; average order value is (100 + 30) / 2 = 65, while the average over the four line rows is 130 / 4 = 32.5.

Distinct counts do not add. Google says GA4 counts a session as a unique combination of user and session ID "regardless of the timeframe", so a session that crosses midnight shows up on two days and once in the period.

How do I check an aggregate on my own data?

Write the grain above each query (one row per order, per campaign per day, per event). Then run the ratio both ways:

SELECT SUM(revenue)                         AS revenue,
       SUM(spend)                           AS spend,
       SUM(revenue) / NULLIF(SUM(spend), 0) AS roas_ratio_of_sums,
       AVG(revenue / NULLIF(spend, 0))      AS roas_average_of_ratios
FROM campaign_days
WHERE day BETWEEN '2026-09-01' AND '2026-09-27';

And count GA4 sessions over the whole period, next to the sum of the daily counts:

WITH s AS (
  SELECT event_date,
         CONCAT(user_pseudo_id, '.',
                CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS session_key
  FROM `your_project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260901' AND '20260927'
)
SELECT COUNT(DISTINCT session_key) AS sessions_whole_period,
       (SELECT SUM(n) FROM (SELECT COUNT(DISTINCT session_key) AS n FROM s GROUP BY event_date)) AS sum_of_daily_counts
FROM s;

Read the output like this:

1. Pass. The ratio of sums equals your total revenue divided by your total spend, and the session count comes from the whole period.
2. The two ROAS columns differ. Your dashboard or the model's query must use the ratio of sums. If it matches the average of ratios, each campaign is weighted the same, however much it spent.
3. sum_of_daily_counts is higher. It is, by the sessions that cross midnight. Report sessions_whole_period.
4. Compare with the GA4 report. The report's figure is an estimate, so an exact BigQuery count within Google's stated precision is consistent with it. A larger gap points to something else, such as filters, modelled data or the time zone.

An LLM can draft this query and explain why the two columns differ. It doesn't know which one your dashboard uses, so run both.

Sources, 30 September 2026: ICL-based text-to-SQL error study (Shen et al., FSE 2026); Spider 2.0 paper (Lei et al., ICLR 2025); Bridge the gap between the Google Analytics UI and BigQuery export (Google for Developers, 2023).

Related answers

• How a join drops or repeats orders
• What NULL does to a total
• Return on ad spend (ROAS)
• Blended ROAS and MER calculator