Answered

2.37 Write an SQL statement to display the WarehouseID and the sum of QuantityOnHand, grouped by WarehouseID. Omit all SKU items that have 3 or more items on hand from the sum, and name the sum TotalItemsOnHandLT3 and display the results in descending order of TotalItemsOnHandLT3.

Answer :

MrRoyal

Answer:

SELECT WarehouseID, SUM(QuantityOnHand) AS TotalItemsOnHandLT3

FROM INVENTORY

GROUP BY WarehouseID

WHERE QuantitiyOnHand < 3

ORDER BY TotalItemsOnHandLT3 DESC

Explanation:

The above code is an SQL code that displays the column WarehouseID and Sum of QuantityOnHand.

But QuantityOnHand is displayed as TotalItemsOnHandLT3.

The displayed columns is grouped by WarehouseID

And Only Rows where QuantityOnHand is less than 3.

The whole output is ordered in descending order by TotalItemsOnHandLT3.

The first line of the codes shows the displayed rows.

The displayed rows are WarehouseID and TotalItemsOnHandLT3.

TotalItemsOnHandLT3 represents Sum(QuantityOnHand).

The next line of the code shows the table name being queried.

The table name is INVENTORY

The third line of the code shows the groupings of the output.

The output is being grouped by WarehouseID

The fourth line of the code shows the rows being used as a condition for the query.

Only rows where QuantityOnHand is not more than 3 (i.e. less than 3)

The fifth row of the code shows the order of arrangement of the output.

The query is being ordered in descending order using TotalItemsOnHandLT3

Other Questions