Grouping by concatenated string azure stream analytics

Question

My query as below :

SELECT
        concat (dummy1,dummy2) as dummydata,
        COUNT(*) as countdata
        FROM events  TIMESTAMP BY EventEnqueuedUtcTime
        GROUP BY HoppingWindow(second,10,5), dummydata

This gives an error: Column 'dummy1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I do not wish to group by the individual columns dummy1 or dummy2 but use the cancatenated data dummydata..

Any way of solvng this..

Thanks

Answer 1

The expressions that you use in select should either be in group by or should be aggregated. This property is similar to SQL.

For example below query works. It groups by concatenated string.

SELECT
    concat (dummy1,dummy2) as dummydata,
    COUNT(*) as countdata
 FROM 
    events TIMESTAMP BY EventEnqueuedUtcTime
 GROUP BY 
    HoppingWindow(second,10,5), 
    concat (dummy1,dummy2)