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.