How do I write SQL for this scenario

Lets consider the orders table in the Northwind database and I need to get the count of order records for the year 1997 and 1998 in a single query how do I do this?

I tried some thing like this...

select COUNT(o.orderid) as 'Count Of Orders 1997', COUNT(o1.orderid) as 'Count Of Orders 1998'
from orders O
inner join orders o1
on o.orderid = o1.orderid
where year(o.orderdate) = 1997
or year(o1.orderdate) = 1998

Please help m开发者_JAVA百科e in this...

---

select COUNT(o.orderid) as 'Count Of years'
from orders O   
where year(o.orderdate) = 1997
or year(o1.orderdate) = 1998 
group by year(o.orderdate)

---

SELECT 
    (SELECT COUNT(*) FROM orders WHERE YEAR(orderdate) = 1997)
    AS [Orders 1997],
    (SELECT COUNT(*) FROM orders WHERE YEAR(orderdate) = 1998)
    AS [Orders 1998]    

---

if you want in one row then you can use pivot function as well

with pivot_table as
(
select orderid, orderdate from orders 
)
select * from pivot_table 
pivot ( count(orderid) as 'count' for to_char(orderdate,'YYYY') in ('1997' , '1998') )

then it will give output like

  1997_count   |   1998_count
<count of 1997>   <count of 1998>

or else you can do folloing for take output in different different rows

  SELECT  to_char(o.OrderDate,'YYYY'), COUNT(o.OrderId) 
    FROM  Orders o
   WHERE  to_char(o.OrderDate,'YYYY') = '1997'  
      or  to_char(o.OrderDate,'YYYY')  = '1998' 
GROUP BY  to_char(o.OrderDate,'YYYY')