CODEWITHSUNDEEP
sqlsql-servert-sql
Arthur Pinhas
Arthur Pinhas

Reputation: 77

SQL using select within a select to get results for each year

I'm working on the northwind database for my SQL studies and I'm trying to display for each year the customer who purchased the highest amount.

It seems to me that I need to do a select within a select to get the result I'm looking work. I`m managing to get the highest amount of orders for a customer, but I can't order it or just seperate it by years.

This is what I managed to do so far:

select top 1 
    (count([Order Details].OrderID)) 'NumOfOrders',
    Customers.CompanyName, year(OrderDate) 'OrderYear'
from 
    [Order Details], Orders, Customers
where 
    [Order Details].OrderID = Orders.OrderID 
    and Orders.CustomerID = Customers.CustomerID 
    and (year(OrderDate) = 1996 or year(OrderDate) = 1997 or year(OrderDate) = 1998)
group by 
    Customers.CompanyName, year(OrderDate)
order by 
    NumOfOrders desc

Upvotes: 1

Views: 157

Answers (2)

Vasily
Vasily

Reputation: 5782

If I've get correct understanding of what you trying to achieve (top 1 of most valuable customer for each year), then you can try this:

SELECT  TOP 1 WITH TIES
        t.NumOfOrders, 
        t.CompanyName, 
        t.OrderYear,
        ROW_NUMBER() OVER (PARTITION BY t.OrderYear 
                            ORDER BY t.NumOfOrders DESC ) AS RN
FROM (  SELECT      COUNT(OD.OrderID) AS [NumOfOrders] ,
                    C.CompanyName,
                    YEAR(OrderDate) AS [OrderYear]
        FROM    [Order Details] AS OD 
                    JOIN [Orders] AS O ON OD.OrderID = O.OrderID
                    JOIN Customers AS C ON O.CustomerID = C.CustomerID
        WHERE  YEAR(OrderDate) IN (1996, 1997, 1998)
        GROUP BY C.CompanyName, YEAR(OrderDate)
     ) AS T
ORDER BY RN

Upvotes: 1

Zeki Gumus
Zeki Gumus

Reputation: 1484

If you remove TOP 1 from your script you will see what you want. But like Gordon said you need to improve your querying skills. I have found some link for you, you can check them :

Sample Script for you :

SELECT 
     C.CompanyName
    ,YEAR(O.OrderDate) AS 'OrderYear'
    ,(COUNT(O.OrderID)) AS 'NumOfOrders'
FROM [Order Details] AS OD
INNER JOIN Orders AS O ON OD.OrderID = O.OrderID
INNER JOIN Customers AS C ON O.CustomerID = C.CustomerID
WHERE   YEAR(O.OrderDate) IN(1996,1997,1998)
GROUP BY C.CompanyName
        ,YEAR(O.OrderDate)
ORDER BY 
        C.CompanyName
        ,YEAR(O.OrderDate)
        ,NumOfOrders DESC

Upvotes: 1

Related Questions