custom SQL QUERY: FINDING TOTAL SALES FOR DISTINCT BRAND AS ATTACHED IN THE XLS

[partha-smsrc]

I have a dataset containing two columns: Brand and Sales. One of the brands, Metformin, appears multiple times, and some sales values (such as 50 and 30) are also repeated.
I want to create a Total column that calculates the sum of sales considering only distinct brands. For example, given sales values of 40, 50, 50, 30, and 30, the total should be 200. However, since Metformin appears more than once, it should only be counted once when calculating this total.
Additionally, I will be writing this logic using custom SQL at the chart level, not directly on the database, so I cannot use a full query structure like SELECT * FROM ....
Dataset (1).xlsx

@dosu

[dosubot[bot]]

Hi @partha-smsrc! I'm Dosu and I'm helping the Apache Superset team.

This is a common question — in fact, an almost identical one was asked previously [1]. Here's the situation:

The core challenge: Superset's custom SQL metric field accepts aggregate expressions (like SUM(...), COUNT(...), etc.) but does not allow full SELECT ... FROM ... queries. Subqueries are also blocked by default [2]. This makes "sum of sales per distinct brand" tricky to express in a single custom SQL metric.

What won't work:

• SUM(DISTINCT Sales) — this sums distinct sales values, not distinct brands. If two different brands both have a sale of 50, it would only count 50 once, which isn't what you want.

Recommended approach — Virtual Dataset:

The most reliable solution is to create a virtual (SQL) dataset in SQL Lab, then build your chart on top of it:

SELECT Brand, SUM(Sales) AS Total_Sales
FROM your_table
GROUP BY Brand

This gives you one row per distinct brand with its total sales. You can then use a Table chart with subtotals enabled to get the grand total, or add a simple SUM(Total_Sales) metric on this virtual dataset.

Alternative — Use dimensions in the chart:

If you add Brand as a dimension (Group By) in your chart configuration and use SUM(Sales) as the metric, Superset will automatically group by brand (deduplicating it). You'll get one row per brand. Enable Show Totals (available in Table charts) to display the overall sum across all distinct brands.

Both approaches avoid needing to hardcode any brand names and work dynamically regardless of how many brands appear multiple times in your data.

To reply, just mention @dosu.

---

Docs are dead. Just use Dosu.

[FrancescoCastaldi]

The reason standard SUM(DISTINCT Sales) does not work is because DISTINCT inside an aggregate deduplicates the numeric sales values themselves rather than the brands. If Brand A has 50 sales and Brand B also has 50 sales, SUM(DISTINCT Sales) drops Brand B's 50 because the value 50 is duplicate.

Because Superset chart-level custom SQL metrics are injected directly into the SELECT <metric> clause, you cannot write a full SELECT ... FROM ... GROUP BY statement inside that field.

Here are the two ways to solve this in Superset:

Method 1: Pre-aggregate in a Virtual Dataset (Recommended)

The cleanest approach in Superset is to create a lightweight virtual dataset:

1. In Superset, go to Data -> Datasets -> + Dataset -> Create Virtual Dataset.
2. Write a query that computes each distinct brand's sales first:

SELECT
  Brand,
  MAX(Sales) AS brand_sales
FROM your_original_table
GROUP BY Brand

3. In your chart, select this virtual dataset.
4. Your metric is now a standard, simple metric:

SUM(brand_sales)

When Metformin appears multiple times, MAX(Sales) collapses it into one record, and the chart's SUM(brand_sales) computes the accurate total across all distinct brands without dropping non-unique sales values.

Method 2: Analytical Window Function (if supported by your SQL Dialect)

If your underlying database (PostgreSQL, Trino, Snowflake, Databricks, ClickHouse) supports window functions inside custom expressions, you can create a Calculated Column on your dataset:

CASE
  WHEN ROW_NUMBER() OVER (PARTITION BY Brand ORDER BY Sales DESC) = 1 THEN Sales
  ELSE 0
END

Name this calculated column distinct_brand_sales. Then in your chart, simply use:

SUM(distinct_brand_sales)

This sets duplicate rows for any brand to 0 while preserving the primary sales entry, producing the expected 200 total cleanly.