Question

Write an SQL statement to display from the Products table the CategoryID, CategoryName, and the sum of Units In Stock grouped by CategoryID, CategoryName and name the sum Total Products OnHand.Only include products that have 100 or lessin stock(Hint: use aWHERE clause). Only show categories having more than 200 total products in stock(Hint: use a GROUP BY and HAVING clause). Display the results in descending order by Total Products OnHand.

Answer 1

Select CategoryID, CategoryName, sum(Units_in_stock) as "Total Products On hand"

from Products

where Units_in_stock <= 100

group by CategoryID, CategoryName

having count(Units_in_stock) > 200

order by count(Units_in_stock) desc;

Step-by-step explanation:

First thing to do is to select the required columns from the Products table. We have used an alias for the "Units_in_stock" column.

Next, is to have the where clause, followed by grouping the results by category Id and name.

And after that, only showing grouped results with more than 200 as the sum value. And finishing it off with the descending order command.