You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@superset.apache.org by "FrancescoCastaldi (via GitHub)" <gi...@apache.org> on 2026/10/24 13:38:04 UTC

Re: [D] custom SQL QUERY: FINDING TOTAL SALES FOR DISTINCT BRAND AS ATTACHED IN THE XLS [superset]

GitHub user FrancescoCastaldi added a comment to the discussion: custom SQL QUERY: FINDING TOTAL SALES FOR DISTINCT BRAND AS ATTACHED IN THE XLS

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:
```sql
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:
```sql
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:

```sql
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:
```sql
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.


GitHub link: https://github.com/apache/superset/discussions/40964#discussioncomment-19164796

----
This is an automatically sent email for notifications@superset.apache.org.
To unsubscribe, please send an email to: notifications-unsubscribe@superset.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org