Find the most popular month for customers to order a certain product

I am trying to find out which month has the most orders for a certain product (Product HHYDP). This is my code so far, but each time I try to use GROUP BY and SORT BY functions related to my problem, I get an error. There are three years in the database (2006,2007,2008) and they are formatted as (YYYY-MM-DD). I am trying to find which month has the highest total order volume across the three years, quantity is irrelevant.

SELECT p.productname, o.orderdate
FROM [Sales].[Orders] as o
    JOIN [Sales].[OrderDetails] as od
        ON o.orderid = od.orderid
    JOIN [Production].[Products] as p
        ON od.productid = p.productid
WHERE p.productname like '%hhydp%'

I am using microsoft SQL management server.

Answer

You can try to use year and month with COUNT aggregate function function and add them in group by

SELECT p.productname,
       year(o.orderdate) yr,
       month(o.orderdate) mn,
       COUNT(*) cnt
FROM [Sales].[Orders] as o
    JOIN [Sales].[OrderDetails] as od
        ON o.orderid = od.orderid
    JOIN [Production].[Products] as p
        ON od.productid = p.productid
WHERE p.productname like '%hhydp%'
GROUP BY p.productname, year(o.orderdate) ,month(o.orderdate)