none
List Sales Totals for all Sales Regions (via the Territories table using 4 joins) with a Sales Total greater than 1,000,000. Use rounding or FORMAT to present the numbers? RRS feed

  • Question

  • Using the Northwind Database I need to run a query that list Sales Totals for all Sales Regions via the Territories table using 4 joins with a sales Total greater than 1,000,000. I am new to SQL and this is what I think the query would look like but I am not sure if I am using the right data and tables or joining them correctly. Can someone just help me and tell me the fields I need and how I should be joining them to get the right result.

    SELECT SUM(od.Quantity * od.UnitPrice) total_sales FROM [Order Details] od
    INNER JOIN Orders o ON o.orderID = od.orderID
    INNER JOIN EmployeeTerritories et ON et.employeeID=od.employeeID
    INNER JOIN Regions t ON t.regionID = et.regionID
    INNER JOIN Territories t ON t.regionID = r.regionID

    UPDATE

    This is the new query i have written however i think this needs to display the region name and i am only getting a total without region names.

    SELECT ROUND(SUM(od.Quantity * od.UnitPrice),2) total_sales FROM [Order Details] od
    INNER JOIN Orders o ON o.orderID = od.orderID
    INNER JOIN Territories t ON t.RegionID = t.RegionID
    INNER JOIN Region r ON r.RegionDescription = r.RegionDescription
    HAVING ROUND(SUM(od.Quantity * od.UnitPrice),2) >100000

    • Edited by JayJ12 Sunday, November 17, 2019 1:39 PM UPDATED query
    Saturday, November 16, 2019 7:14 PM

All replies

  • >>Using the Northwind Database I need to run a query that list Sales Totals for all Sales Regions via the Territories >>>table using 4 joins with a sales Total greater than 1,000,000

    If you need only total for all regions so  and do not need additional columns the try the below

    SELECT total_sales 

    FROM

    (

    SELECT SUM(od.Quantity * od.UnitPrice) total_sales FROM [Order Details] od
    INNER JOIN Orders o ON o.orderID = od.orderID

    ) WHERE total >1000000


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, November 17, 2019 5:39 AM
    Moderator
  • Let's first get the join order right. The sales amount in Order Details, so this is where you start. Then you need to go to the Orders table to find the employee. Next, you need to find the territories for the employees, and this is EmployeeTerritories. This is also what you have in your first query, whereas the second is missing this table.

    You also need to join by the correct column, so far you have it almost right, but the join on EmployeeTerritories should have o.EmployeeID, not od, since the EmployeeID is on Orders, not Order Details.

    From EmployeeTerritories, you need to go to the territory to find the region. You are trying to it in reverse order, but this fails since there is no RegionID in EmployeeTerritories. Finally, you go from Territories to Region to get the name of the Region.

    Part of this exercise is to understand the structure of all the tables, and to find which columns are where. You can use Database Diagrams to get a graphical representation. Myself, being of the old school, I often simply use sp_help:

    sp_help Orders

    Anyway, right now we have:

    SELECT ROUND(SUM(od.Quantity * od.UnitPrice),2) total_sales 
    FROM  [Order Details] od
    INNER JOIN Orders o ON o.OrderID = od.OrderID
    INNER JOIN EmployeeTerritories et ON et.EmployeeID = o.EmployeeID
    INNER JOIN Territories t ON t.TerritoryID = et.TerritoryID
    INNER JOIN Region r ON r.RegionID = t.RegionID
    HAVING ROUND(SUM(od.Quantity * od.UnitPrice),2) >100000

    But here we have a total of all orders, and the tables beyond Order Details do not really have any meaning. We want a total per Region. The secret here is the GROUP BY clause:

    SELECT r.RegionDescription, ROUND(SUM(od.Quantity * od.UnitPrice),2) total_sales 
    FROM  [Order Details] od
    INNER JOIN Orders o ON o.OrderID = od.OrderID
    INNER JOIN EmployeeTerritories et ON et.EmployeeID = o.EmployeeID
    INNER JOIN Territories t ON t.TerritoryID = et.TerritoryID
    INNER JOIN Region r ON r.RegionID = t.RegionID
    GROUP  BY r.RegionDescription
    HAVING ROUND(SUM(od.Quantity * od.UnitPrice),2) >100000

    The GROUP BY clause tells SQL Server that we want the sum grouped by the column we give. So this permits us to get the sales grouped by region. And then the HAVING clause, which you arrived at yourself,  filters out the regions with low sales.

    There are still two errors in the query. One is trivial  - you are missing a 0 in the filter amount. The second error is that your sales amount is too high. To wit, there is a discount column in Order Details, which you should apply to get the correct result. I leave this as an exercise.

    There is also one potential problem. The way the EmployeesTerritories table is defined, a territory could be shared by several employees. With the query above, we would count the same order twice or more which would inflate the sales. This is certainly not trivial to handle, and I think for a beginnner's exercise, this is something we simply ignore. And thankfully, in Northwind, all Territories have at most one employee, so it is not a real issue. I still like to point this out, because next time when you encounter a junction table it may be in real life, and in that case you should react and not just code blindly.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, November 17, 2019 6:05 PM
    Moderator