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

Question

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

*Never* use commas in the `FROM` clause. *Always* use proper, explicit, **standard** `JOIN` syntax. — Gordon Linoff, Dec 10 '18 at 12:06
I`m not really sure what a dbms is. i`m using Microsoft SQL Server Management Studio if that helps. — Arthur Pinhas, Dec 10 '18 at 12:34
@ArthurPinhas How you want to order it. Can you provide expected result. — Zeki Gumus, Dec 10 '18 at 12:39
I need it to show the customer that made the most orders,and seperate them by years. in my database orders where made between the years 1996-1998. so i need to find the customer that made the most orders for 1996,and then the customer that made the most orders for 1997 and so on — Arthur Pinhas, Dec 10 '18 at 12:40
does your sql course really still teaches this deprecated join style ? If so I would suggest looking for a more modern course... — GuidoG, Dec 10 '18 at 12:45
@GuidoG I`m guessing it's not a good way to join tables? i was sent to the course through my current work place actually. you usually do the joins in the FROM clause and not in the WHERE? it seemed to make sense when they taught us to do the joins in the WHERE. — Arthur Pinhas, Dec 10 '18 at 12:48
joining should be done using the standard join syntax. Example : `select orders.Id, Customers.Name from Orders inner join Customers on Orders.CustomerId = Customers.CustomerId` — GuidoG, Dec 10 '18 at 12:51
and also uses aliases, they are very handy when maintaining your query. example `Select o.OrderId from Orders o` — GuidoG, Dec 10 '18 at 12:52
Is it "highest amount", i.e. sum of order values, or "most orders"? — HABO, Dec 10 '18 at 13:13
@HABO well the task was "Display for each year, the customer who purchased the highest amount" so i thought they meant they highest amount of orders. but it could be the highest amount of products? — Arthur Pinhas, Dec 10 '18 at 13:20

Zeki Gumus · Answer 1 · 2018-12-10T13:01:00.943

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

You are right. I have missed it. Thanks for double checking. — Zeki Gumus, Dec 10 '18 at 13:00

score 1 · Accepted Answer · answered Dec 10 '18 at 13:08

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

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

2 Answers2