SQL subquery/join

Question

Hello,

Well I am a beginner in SQL so plz excuse if this problem is too novice for you. In Northwind (Am sure everyone is aware of Northwind) - I an trying to get for each month the product that sold (by quantity) the most and the one that sold the least in the year 1997 (usind orders, order details, product tables)

Thanks

UPDATE: This is what I have so far

Select p.productname, 
       Datepart(MM,o.OrderDate), 
       od.Quantity 
from orders o 
inner join [Order Details] od 
  on o.OrderID = od.OrderID 
inner join Products p 
  on p.ProductID=od.ProductID 
Where YEAR(o.OrderDate)=1997 
Group by Datepart(MM,o.OrderDate), od.Quantity,p.productname

Answer 1

It's hard to tell what you're asking, especially when you don't provide any schemas for your tables. To get started in learning JOIN statements in SQL, however, I'd suggest Jeff Atwood's Visual Guide

Answer 2

This isn't a novice problem. It's a common one, but it's also one people sweat fairly hard to get right. And they often resort to multiple SQL statements.

But you, my friend, are in luck! You are about to come with me into the world of analytic functions. But rest assured - there are other ways to tackle this in case this isn't to your taste.

I'm assuming you're just tinkering around with SQL, and just want enough to learn more. If you are actually trying to solve a specific problem, it would help if you could be more specific on these two points:

1. What do you want your result set to look like? Are you expecting 12 rows, with "Month", "Top Seller", "Bottom Seller" columns? Is 24 rows in one result set OK? Is two result sets OK?
2. Does "sold the least" actually include products that didn't sell anything? (I.e., they donn't appear in the Order Details table for the month at all.)

The SQL:

WITH
    ProductSalesPerMonth AS
    (
        SELECT
            DATEPART(MM, O.OrderDate) AS MonthNum,
            OD.ProductID,
            SUM(OD.Quantity) AS Quantity
        FROM
            Orders O
            JOIN [Order Details] OD
            ON O.OrderID = OD.OrderID
        WHERE
            O.OrderDate BETWEEN '19970101' AND '19971231'
        GROUP BY
            DATEPART(MM, O.OrderDate),
            OD.ProductID
    ),
    RankedProductSalesPerMonth AS
    (
        SELECT
            PSPM.*,
            ROW_NUMBER() OVER (PARTITION BY PSPM.MonthNum ORDER BY PSPM.Quantity ASC) AS Rank_SoldLeast,
            ROW_NUMBER() OVER (PARTITION BY PSPM.MonthNum ORDER BY PSPM.Quantity DESC) AS Rank_SoldMost
        FROM
            ProductSalesPerMonth PSPM
    )
SELECT
    PS.MonthNum,
    P.ProductName,
    PS.Quantity,
    CASE
        WHEN Rank_SoldMost = 1 THEN 'Top Seller'
        WHEN Rank_SoldLeast = 1 THEN 'Bottom Seller'
        ELSE 'Only Seller'
    END AS SalesRank
FROM
    RankedProductSalesPerMonth PS
    JOIN Products P
    ON P.ProductID = PS.ProductID
WHERE
    (Rank_SoldMost = 1 OR Rank_SoldLeast = 1)
ORDER BY
    MonthNum ASC,
    Quantity DESC
;

The output:

MonthNum ProductName           Quantity   SalesRank 
1        Geitost               119        Top Seller
1        Konbu                 2          Bottom Seller
2        Pâté chinois          180        Top Seller
2        Gorgonzola Telino     3          Bottom Seller
3        Raclette Courdavault  162        Top Seller
3        Konbu                 1          Bottom Seller

Maybe you're seeing this and saying "Jeezuz!", so here's a quick explanation of the code.

WITH lets you put together a kind of subquery called a "Common Table Expression". In SQL Server, it is supposed to work kind of like a view. CTEs can reference each other. I'm using them because I think it's easier to understand the individual components than one big SELECT where you refer to DATEPART(MM, O.OrderDate) and SUM(OD.Quantity) everywhere. It also lets us defer joining tables (like Product) until we actually need that information.

Our first clause does our grouping and summing per month, per product. That's all.

The second clause identifies the top and bottom sellers. In SQL Server, a common way to find one record out of a group is to partition and sort so that the record you want bubbles to the top of a ranked list. By sorting by Quantity DESC, our top seller will get a "1" in the Ranked_SoldMost column. Similar for bottom seller.

The third clause lets us filter down to our top/bottom sellers and then decorate the ProductID with the Product information.

And that's it. If this isn't what you were asking for or you have questions, fire away.

Answer 3

I have come up with a solution that solves your problem. It is a select statement with 3 subselects but it works and has the most and least sold product on the same row:

select  
    a.month,
    (select top 1 
            p.ProductName as pid 
        from Northwind.dbo.[Order Details] od1 
            left join Northwind.dbo.Orders o on  o.OrderID = od1.OrderID 
            left join Northwind.dbo.Products p on p.ProductID = od1.ProductID 
        where 
            YEAR(o.OrderDate)=1997 and 
            DATEPART(MM,o.OrderDate) = a.month 
        group by 
            p.ProductName,
            DATEPART(MM,o.OrderDate) 
        having 
            sum(od1.Quantity) = MAX(a.qty)), 
    max(a.qty) as maxQty,
    (select top 1 
            p.ProductName as pid 
        from Northwind.dbo.[Order Details] od1 
            left join Northwind.dbo.Orders o on  o.OrderID = od1.OrderID 
            left join Northwind.dbo.Products p on p.ProductID = od1.ProductID 
        where 
            YEAR(o.OrderDate)=1997 and 
            DATEPART(MM,o.OrderDate) = a.month 
        group by 
            p.ProductName,
            DATEPART(MM,o.OrderDate) 
        having 
            sum(od1.Quantity) = MIN(a.qty)), 
    min(a.qty) as minQty
from 
    (select 
            p.ProductName as pid,
            DATEPART(MM,o.OrderDate) as month, 
            sum(od1.Quantity) as qty 
        from Northwind.dbo.[Order Details] od1 
            left join Northwind.dbo.Orders o on  o.OrderID = od1.OrderID 
            left join Northwind.dbo.Products p on p.ProductID = od1.ProductID 
        where 
            YEAR(o.OrderDate)=1997 
        group by 
            p.ProductName,
            DATEPART(MM,o.OrderDate)) as a 
group by 
    a.month

I am sure something more clever could have been done using some temporary tables