cube-js / cube-js/cube

group by error when using filters with distinct count

Open
#7,053 1 comment 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

I'm getting the following error:

`Column 'ABC__m_i_key__ABC__m_i.Age_Group5' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.`

The measure:

` measures: {
_Deceased: { type: 'number', sql: 'COUNT(DISTINCT DW_ID_Key)', filters: [{sql: '${CUBE.Age_Group5} = "00. Deceased"'}] },
},`

Changing to normal count works 100% as expected.

The SQL being generated:

```
SELECT
TOP 10000 CASE
WHEN ("ABC__m_i"."Age_Group5" = '00. Deceased') THEN COUNT(DISTINCT DW_ID_Key)
END "ABC__m_i____deceased"
FROM
(
SELECT
a.*,
ISLEAD,
Prem1,
CASE
WHEN Age_Group5 IS NOT NULL THEN Age_Group5
ELSE 'NA'
END AS Age_Group5,
CAST(GETDATE() AS DATE) AS CurrentDate
FROM
"ABC"."Link_Prod" a
LEFT JOIN "ABC"."Members_Prod" b on a.DW_ID_KEY = b.DW_ID_KEY
LEFT JOIN "ABC"."STRIVE_CORE" d on a.DW_ID_KEY = d.DW_ID_KEY
WHERE
a.DW_ID_KEY IS NOT NULL
) AS "ABC__m_i"
```

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.