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 [email protected]. To unsubscribe, please send an email to: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
