ClickHouse DB::Exception: Unknown totals mode

The UNKNOWN_TOTALS_MODE error occurs when the totals_mode setting is assigned a value that ClickHouse does not recognize. The message typically reads DB::Exception: Unknown totals mode: 'invalid_value'. The totals_mode setting controls how ClickHouse calculates the totals row when using GROUP BY ... WITH TOTALS, and it only accepts a specific set of values.

Impact

Any query or session that sets totals_mode to an unrecognized value will fail. If the invalid value is set in a user profile in users.xml, all queries from affected users that use GROUP BY WITH TOTALS may behave unexpectedly, or the profile itself may fail to load. The totals row calculation is important for reporting and analytical queries that rely on aggregate summaries alongside grouped results.

Common Causes

1. Typo in the setting value -- Writing 'after_having_auto' with extra underscores or misspelling.
2. Using a value from a different ClickHouse version -- Older or newer versions may have different supported values.
3. Confusing totals_mode with other enum settings -- Using values that belong to other settings like overflow_mode or distributed_product_mode.
4. Case sensitivity -- The values are case-sensitive and must be in the expected format.

Troubleshooting and Resolution Steps

1. Review the valid totals_mode values: ClickHouse supports the following values for totals_mode:

   • 'before_having' -- Calculate totals across all rows before applying the HAVING clause. This means the totals row includes rows that HAVING would filter out.
   • 'after_having_inclusive' -- Include all rows that pass HAVING in the totals calculation. Rows that are excluded by HAVING are not counted in totals.
   • 'after_having_exclusive' -- Include only rows whose groups pass HAVING in the totals, but calculate totals from the original data for those groups.
   • 'after_having_auto' -- (Default) Automatically chooses between inclusive and exclusive based on whether HAVING removes more than a certain percentage of rows.

2. Set the correct value:

   -- Check current value
   SELECT name, value FROM system.settings WHERE name = 'totals_mode';
   
   -- Set a valid value
   SET totals_mode = 'after_having_auto';
   

3. Fix the value in configuration if needed: If the error originates from a profile in users.xml:

   grep -rn 'totals_mode' /etc/clickhouse-server/users.xml /etc/clickhouse-server/users.d/
   

   Update the value to one of the valid options listed above.

4. Test with a simple query:

   SET totals_mode = 'before_having';
   
   SELECT
       region,
       sum(amount) AS total_amount
   FROM orders
   GROUP BY region WITH TOTALS
   HAVING total_amount > 1000;
   

   The totals row will appear at the end of the result set.

5. Check your ClickHouse version for supported values:

   SELECT version();
   

   The four values listed above have been stable across ClickHouse versions for a long time, but always verify against the documentation for your specific release.

Best Practices

• Use 'after_having_auto' (the default) unless you have a specific reason to change it. It provides reasonable behavior for most use cases.
• Understand the difference between before_having and after_having_* modes before choosing one -- they produce different totals when a HAVING clause is present.
• Document the totals_mode choice in queries or profiles that override the default, so other team members understand the intent.
• When writing reports that depend on the totals row, verify the mode being used and test with representative data.

Frequently Asked Questions

Q: What is the difference between after_having_inclusive and after_having_exclusive?
A: With after_having_inclusive, the totals row is computed from all rows that belong to groups passing the HAVING filter. With after_having_exclusive, the totals row reflects the raw aggregate values for those groups (before HAVING modification). In practice, the difference is subtle and matters mainly when HAVING filters interact with the aggregation in non-trivial ways.

Q: When should I use before_having?
A: Use before_having when you want the totals row to represent the grand total of all data, regardless of the HAVING filter. This is useful in reports where the totals row should show the overall sum while individual rows are filtered.

Q: Does totals_mode affect queries without WITH TOTALS?
A: No. The setting only takes effect when your query includes GROUP BY ... WITH TOTALS. Without WITH TOTALS, the setting is ignored.

Q: How does after_having_auto decide which mode to use?
A: It uses the totals_auto_threshold setting (default 0.5). If the HAVING clause removes more than 50% of the groups, it behaves like after_having_inclusive. Otherwise, it behaves like after_having_exclusive. You can adjust the threshold with SET totals_auto_threshold = 0.5.