Can't use case/when based on measures: support various types and case for measures
- 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
Assessment
This issue has not been assessed yet.