By BCaruso


2019-12-02 23:00:40 8 Comments

I've been tasked with the following Query to design:

"Out of the products that sold units in 2019 (that is, that sold at least one unit), we want to know the product that sold the least number of units. Your table must show:

  •  Product ID
  •  Product name
  •  Product’s category name "

I have included a screenshot of the database.

I am, in fact, able to retrieve the value corresponding to the minimum units sold [95 in this case], but cannot also display the corresponding product name to that value in MS Access.

Using the following code displays the correct minimum value:

SELECT MIN(UnitsSold)
FROM 
     (SELECT Products.ProductID, ProductName, Categories.CategoryID, CategoryName, SUM(Quantity) AS UnitsSold 
     FROM Orders, OrderDetails, Products, Categories 
     WHERE Orders.OrderID = OrderDetails.OrderID AND OrderDetails.ProductID = Products.ProductID And Products.CategoryID = Categories.CategoryID AND OrderDate BETWEEN #01/01/2019# AND #12/31/2019# 
     GROUP BY Products.ProductID, ProductName, Categories.CategoryID, CategoryName 
     HAVING SUM(Quantity) >= 1);

But when I try to also display that product with this modification:

SELECT MIN(UnitsSold, Products.ProductID, ProductName
FROM 
     (SELECT Products.ProductID, ProductName, Categories.CategoryID, CategoryName, SUM(Quantity) AS UnitsSold 
     FROM Orders, OrderDetails, Products, Categories 
     WHERE Orders.OrderID = OrderDetails.OrderID AND OrderDetails.ProductID = Products.ProductID And Products.CategoryID = Categories.CategoryID AND OrderDate BETWEEN #01/01/2019# AND #12/31/2019# 
     GROUP BY Products.ProductID, ProductName, Categories.CategoryID, CategoryName 
     HAVING SUM(Quantity) >= 1);

I am met with the error:

Your query does not include the specified expression 'ProductID' as part of an aggregate function

SQL DataBase

1 comments

@GMB 2019-12-02 23:05:44

Why not just use ORDER BY and TOP 1?

SELECT TOP (1)
    p.ProductID, 
    ProductName, 
    c.CategoryName, 
FROM 
    Orders o
    INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
    INNER JOIN Products p ON od.ProductID = p.ProductID
    INNER JOIN Categories c ON p.CategoryID = c.CategoryID
WHERE OrderDate BETWEEN #01/01/2019# AND #12/31/2019# 
GROUP BY 
    p.ProductID, 
    ProductName, 
    c.CategoryName 
HAVING SUM(Quantity) >= 1
ORDER BY SUM(Quantity) DESC

Notes:

  • always use explicit joins (with the ON keyword) instead of implicit joins (with commas in the FROM clause), whose syntax is harder to follow and has fallen out of favor more than 20 years ago

  • table aliases make the query easier to write and read; also, in a multi-table query, you want to qualify all column names (there are a few table prefixes missing in your query).

@BCaruso 2019-12-03 01:17:36

Hey this worked great! I had to tweak the Joins, as MS Access doesn't allow this syntax. My final joins were: ((( Orders AS O INNER JOIN OrderDetails AS OD ON O.OrderID = OD.OrderID ) INNER JOIN Products AS P ON OD.ProductID = P.ProductID ) INNER JOIN Categories AS C ON P.CategoryID = C.CategoryID )

Related Questions

Sponsored Content

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

47 Answered Questions

[SOLVED] How do I import an SQL file using the command line in MySQL?

39 Answered Questions

27 Answered Questions

[SOLVED] How do I perform an IF...THEN in an SQL SELECT?

41 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

10 Answered Questions

[SOLVED] Select multiple columns from a table, but group by one

  • 2014-01-19 14:08:48
  • Ozan Ayten
  • 130186 View
  • 39 Score
  • 10 Answer
  • Tags:   sql group-by

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

47 Answered Questions

29 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2663661 View
  • 1802 Score
  • 29 Answer
  • Tags:   sql duplicates

Sponsored Content