cube-js / cube-js/cube

Can't use case/when based on measures: support various types and case for measures

Open
#260 5 comments 0 reactions 0 assignees View on GitHub
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

**Describe the bug**
I have the schema:

```
cube(`Trades`, {
...

measures: {
...

buysCount: {
sql: `${CUBE}."id"`,
type: 'count',
filters: [
{ sql: `${CUBE}."side" = 'buy'` }
]
},

sellsCount: {
sql: `${CUBE}."id"`,
type: 'count',
filters: [
{ sql: `${CUBE}."side" = 'sell'` }
]
},
},

dimensions: {
signal: {
type: `string`,
case: {
when: [
{ sql: `${buysCount} >= 50`, label: `1` },
{ sql: `${sellsCount} >= 50`, label: `2` },
],
else: { label: `0` }
}
}
}
});
```

And I can't use these measures in `case when`, bacause cubejs generating SQL like:

```
SELECT
CASE
WHEN count(CASE WHEN (trades."side" = 'buy') THEN trades."id" END) >= 50 THEN '1'
WHEN count(CASE WHEN (trades."side" = 'sell') THEN trades."id" END) >= 50 THEN '2'
ELSE '0' END "trades.signal",
count(CASE WHEN (trades."side" = 'buy') THEN trades."id" END) "trades.buys_count",
count(CASE WHEN (trades."side" = 'sell') THEN trades."id" END) "trades.sells_count"
FROM
(
...mainSQL
) AS trades
GROUP BY 1 LIMIT 5000
```

It adds `GROUP BY` statement by default for dimensions and the error here:
`Error: aggregate functions are not allowed in GROUP BY`

**Expected behavior**
Without `GROUP BY 1` my query works as expected. May be it's ok to add options to skip grouping for dimensions or allow `case when` in measures

**Version:**
0.11.18

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.