Custom Sql query Problem

[Aruneem]

1. I have a dataset with two columns: Brand and Sales.
2. One brand, Metformin, appears multiple times. Some sales values are also repeated (50, 30).
3. I want to create a Total column that shows the sum of sales for distinct brands. For example, the sales values are 40, 50, 50, 30, and 30,
   which add up to 200. Since Metformin is repeated twice, it should be considered only once.
4. I will be writing this query in custom SQL at the chart level, not on the actual database, so I cannot use SELECT * FROM ...

Dataset.xlsx

@dosu

[dosubot[bot]]

Hi @Aruneem, you can achieve this using a custom SQL metric in the chart editor. Since you want the sum of sales for distinct brands (treating repeated brands as one), you can use a combination of SUM and CASE or leverage COUNT(DISTINCT ...) logic.

Here's an approach using custom SQL in the metric field:

To get the total sales considering each brand only once (i.e., sum of sales per distinct brand):

You'd write a custom SQL metric like:

SUM(CASE WHEN Brand = 'Metformin' THEN Sales ELSE 0 END) 

However, since your goal is to sum sales for distinct brand occurrences across the dataset (essentially deduplicating repeated rows), the cleanest approach at the chart level is:

1. Create a custom SQL metric in the Metrics section of the chart editor using:

SUM(DISTINCT Sales)

⚠️ Important caveat: SUM(DISTINCT Sales) sums distinct sales values, not distinct brands. In your example, if Metformin has sales of 50, 50, 30, 30, 40, then SUM(DISTINCT Sales) would give 50 + 30 + 40 = 120, which removes duplicate values regardless of brand.

If what you actually want is the sum of one representative sale per distinct brand, you'd need a different approach:

2. Group by Brand first — In the chart configuration, add Brand as a dimension/group-by column and use SUM(Sales) as the metric. This gives you total sales per brand. Then to get an overall total, you can use a post-processing step or a Table chart with subtotals enabled.

3. Use a derived virtual dataset — Create a new virtual dataset in SQL Lab with:

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

   Then build your chart on top of this virtual dataset.

Option 3 (virtual dataset) is typically the most reliable way to handle deduplication logic that doesn't fit neatly into a single aggregate expression [1].

Could you clarify exactly which rows should be deduplicated? For instance, should all rows for "Metformin" collapse into one sum, or should only exact duplicate rows (same Brand + same Sales) be removed?

To reply, just mention @dosu.

---

Share context across your team and agents. Try Dosu.

[Aruneem]

1. SUM(CASE WHEN Brand = 'Metformin' THEN Sales ELSE 0 END) calculates sales totals for the brand Metformin.
2. Metformin is only used as an example; the dataset actually contains many different brand names.
3. Several brands appear multiple times in the dataset, so the query must handle repeated entries.
   You cannot hardcode Metformin into the query, since the goal is to work across all brands dynamically.
4. The query should be written at the Custom SQL level itself, without altering the database schema.
   Avoid queries that involve SELECT *; the solution must remain precise and controlled.

[Aruneem]

Let me Reframe this question and make it more simple

1. In my dataset you can see that Metformin has appeared twice , but at the custom sql you cannot hardcode it by mentioning Metformin , because in actual dataset there are various Brands that are appearing multiple times ,so SUM(CASE WHEN Brand = 'Metformin' THEN Sales ELSE 0 END) cannot be applied.
2. Query that i need to provide is at the custom sql level , that is at the chart level , without changing anything in the actual database,
   so queries like select * from .. is not allowed because such queries are written at the database level not at the custom sql level.