Group by using Azure ML

Question

Hello,

I have a dataset with a column "date", and i want to do a group by "month".
How can i do it ?

thanks

Answer 1

Hello @22176499

Thanks for reaching out to us, you can use Apply SQL Transformation module and leverage SQL to do it.

As above screenshot, you can add a Apply SQL Transformation module. One way to group by month is leveraging below scheme -

SELECT  Closing_Date = DATEADD(MONTH, DATEDIFF(MONTH, 0, Closing_Date), 0), 
        Category,  
        COUNT(Status) TotalCount 
FROM    MyTable
WHERE   Closing_Date >= '2012-02-01' 
AND     Closing_Date <= '2012-12-31'
AND     Defect_Status1 IS NOT NULL
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Closing_Date), 0), Category;

I refer to the thread here - https://stackoverflow.com/questions/14565788/how-to-group-by-month-from-date-field-using-sql

Please take a look and do it according to your data format, I hope this helps.

Regards, Yutong -Please kindly accept the answer if you feel helpful to support the community, thanks a lot.