Unable to group by DATE_TRUNC

This query gives the error - Expression 'my_table.dob' is not being grouped

SELECT DATE_TRUNC('day', dob) AS dob,
       group_name AS group_name,
       location_name AS location_name,
       sum(amount) AS net
FROM  my_table
WHERE dob >= TO_DATE('2021-09-03', 'YYYY-MM-DD')
  AND dob < TO_DATE('2021-09-10', 'YYYY-MM-DD')
GROUP BY DATE_TRUNC('day', dob),
         group_name,
         location_name
ORDER BY net DESC
LIMIT 10000

But if I give the column a different alias, it works -

SELECT DATE_TRUNC('day', dob) AS d,
       group_name AS group_name,
       location_name AS location_name,
       sum(amount) AS net
FROM  my_table
WHERE dob >= TO_DATE('2021-09-03', 'YYYY-MM-DD')
  AND dob < TO_DATE('2021-09-10', 'YYYY-MM-DD')
GROUP BY DATE_TRUNC('day', dob),
         group_name,
         location_name
ORDER BY net DESC
LIMIT 10000

My visualization tool [Superset] uses an alias of the original column name, so dynamically changing the datetime granularity is broken.
I’ll raise an issue with Superset, but I think a better fix is to enable a column alias with same name as the original value.

@jdbranham Was able to reproduce the behavior, will check internally why this behavior and get back to you

1 Like