SQL Help-Coding for Years

Question

8) Rerun the same report and give me numbers for 2014 and 2015 for each the three companies. Order it by year and then highest to lowest number of orders within each year.

SELECT count(orders.orderid)
,      customers.companyname
,      to_char(orders.orderdate, 'Y') as Year
FROM   northwinds.orders 
JOIN   northwinds.customers 
ON     customers.customerid = orders.customerid 
JOIN   northwinds.orderdetails 
ON     orderdetails.orderid = orders.orderid 
WHERE  orders.orderdate between '1-JAN-14' and '31-DEC-15' 
AND    customers.companyname = 'Rattlesnake Canyon Grocery' 
OR     customers.companyname= 'Around the Horn' 
OR     customers.companyname='Island Trading'
GROUP 
BY     customers.companyname 
AND    to_char(orders.orderdate, 'Y') 
ORDER 
BY     count(orders.orderid) DESC
,      to_char(orders.orderdate, 'Y')
;

When I run this, I receive the error:

Error: [Oracle][ODBC][Ora]ORA-00933: SQL command not properly ended

Answer 1

GROUP BY customers.companyname AND to_char(orders.orderdate, 'Y') 
                               ^^^ replace with a comma

Answer 2

As FuzzyTree stated replacing the AND with a comma is the starting point.

SQL Server does not have a to_char function. As I have not used ORACLE to call SQL Server I am not sure if some form of translation takes place or whether this will result in an error. If it does produce an error you can use one of the following instead:-

GROUP BY customers.companyname ,  YEAR(orders.orderdate) 
ORDER BY count(orders.orderid) DESC, YEAR(orders.orderdate) ;

or

GROUP BY customers.companyname ,  CONVERT(CHAR(4),orders.orderdate,120) 
ORDER BY count(orders.orderid) DESC, CONVERT(CHAR(4),orders.orderdate,120);

The 120 means ODBC Canonical. See https://msdn.microsoft.com/en-GB/library/ms187928.aspx

I have found that the CONVERT statement can outperform the YEAR() function, which is somewhat counter-intuitive.