CODEWITHSUNDEEP
sql-server
master2080
master2080

Reputation: 371

Find the number of orders of every year

I am new with SQL and I would like some help.

I am using MSSQL (Northwind database).

How is it possible to get all the orders of each year?

I know how to get every year from the order dates, but I have no idea how to approach on getting the total number of orders per year:


SELECT datepart(year,o.OrderDate)
FROM Orders o
   JOIN [Order Details] od ON o.OrderID = od.OrderID
   group by datepart(year,o.OrderDate)

Can anyone tell me where to look or provide some help?

There are a total of three years that come out as a result(1999,1998,1997)

There is a total of 830 orders in the database.

Doing a count(*) produces (691,405,1059) which is way off.

The tables:

Tables

Edit: The reason I am using join is because I will need the combined price of all orders per year as well.

Thanks.

Upvotes: 2

Views: 6519

Answers (2)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try this : Use count Distinct for avoiding duplicate Order ID's

SELECT COUNT(DISTINCT o.OrderID),YEAR(o.OrderDate)
FROM Orders o
JOIN [Order Details] od ON o.OrderID = od.OrderID 
GROUP BY YEAR(o.OrderDate)

Upvotes: 2

M.Ali
M.Ali

Reputation: 69524

Simply do a Count(*) and your group by is fine as it is.

SELECT datepart(year,o.OrderDate)
      , COUNT(*) TotalOrders
FROM Orders o
   JOIN [Order Details] od ON o.OrderID = od.OrderID
   group by datepart(year,o.OrderDate)

You can also write the same query as:

SELECT year(o.OrderDate) [Year]
      , COUNT(*) TotalOrders
FROM Orders o
   JOIN [Order Details] od ON o.OrderID = od.OrderID
   group by year(o.OrderDate)

Just a different Datetime function but does the same thing.

EDIt

If you just want to know the Orders per Year , you do not need to join it to the Order Details table , simply do the following : 

SELECT year(o.OrderDate) [Year]
      , COUNT(*) TotalOrders
FROM Orders o
group by year(o.OrderDate)

Upvotes: 1

Related Questions