group by error when using filters with distinct count
- 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
Assessment
This issue has not been assessed yet.