Hi Team I have this query in azure, what the script is trying to do is to get a date from current stamp. using select to char(sysdate, 'DD/MM-YYY HH:MI:SS AM') from dual). SELECT illuminance as illuminance, temperature as temperature, ( select to_char(sysdate, 'DD/MM/YYYY HH:MI:SS AM' ) from dual) AS Date INTO [PowerBIVisualizationOutput] FROM [PowerBIVisualizationInput] GROUP BY illuminance, temperature, TumblingWindow( minute ,5) But i am getting an error that group by with no aggregation expression is not supported. What could i improve from this logic? Please assist and provide an improvement based on what i have.

Hi, The group by rule is whatever column you are using in selecting which is not having in aggregation function like COUNT, SUM, AVG etc then those columns should come after group by Clause. if you review your query you will find (select to_char(sysdate,'DD/MM/YYYY HH:MI:SS AM') from dual) is missing in Group by Clause. Please, re-write the query tor resolve it either remove this column or add in Group by clause SELECT illuminance as illuminance, temperature as temperature, ( select to_char(sysdate, 'DD/MM/YYYY HH:MI:SS AM' ) from dual) AS Date INTO [PowerBIVisualizationOutput] FROM [PowerBIVisualizationInput] GROUP BY illuminance, temperature, TumblingWindow( minute ,5)

Answer

Group by with no aggregation expression is not supported

Guest User

680

Hi Team

I have this query in azure, what the script is trying to do is to get a date from current stamp. using select to char(sysdate, 'DD/MM-YYY HH:MI:SS AM') from dual).

SELECT illuminance as illuminance, temperature as temperature, (select to_char(sysdate,'DD/MM/YYYY HH:MI:SS AM') from dual) AS Date INTO [PowerBIVisualizationOutput] FROM [PowerBIVisualizationInput] GROUP BY illuminance, temperature, TumblingWindow(minute,5)

But i am getting an error that group by with no aggregation expression is not supported. What could i improve from this logic? Please assist and provide an improvement based on what i have.