search

cube-js / cube

📊 Cube — The Semantic Layer for Building Data Applications
https://cube.dev
Other
17.71k stars 1.75k forks source link

SQL API failed - sum + case when #8309

Open egisberto opened 3 months ago

egisberto commented 3 months ago

Failed SQL SELECT sum(CASE WHEN indicator_nm IN ('[REPLACED]') THEN 1 ELSE 0 END) AS asdf FROM pregnants_view LIMIT 1001

Logical Plan

Version: 0.34.52

Additional context I try to execute this simple query to SUM values and after this make a division by the total values. (percent of certain indicator_nm). I'm using Superset as dashboard creator and thia is the one way that I found to make the percentage works on Superset. (https://docs.preset.io/docs/using-metrics-and-calculated-columns)

The final query wold be:

SELECT (CAST(sum(CASE WHEN indicator_nm = 'odontoCare' THEN 1 ELSE 0 END) AS FLOAT) / CAST(count(1) AS FLOAT)) * 100

paveltiunov commented 3 months ago

@egisberto Such query should be handled by https://cube.dev/docs/product/apis-integrations/sql-api/query-format#query-pushdown if indicator_nm is dimension and there's count measure is defined on a view.