Brendan Gooden Brendan Gooden - 7 months ago 11
SQL Question

How could I aggregate a description column?

I've written a SQL query that calculates stock totals in each of our warehouses, as follows.

SELECT
InventoryItem.ItemName as 'Part Number',
InventoryItemDescription.ItemDescription as 'Description',
InventoryStockTotal.UnitsInStock as 'In Stock',
InventoryStockTotal.WarehouseCode AS 'Warehouse',
InventoryItem.Status,
InventoryItem.AverageCost AS 'Average Cost',
(InventoryItem.AverageCost * UnitsInStock) AS 'Stock Value'


FROM dbo.InventoryItemDescription
INNER JOIN dbo.InventoryItem
ON InventoryItemDescription.ItemCode = InventoryItem.ItemCode
INNER JOIN dbo.InventoryStockTotal
ON InventoryStockTotal.ItemCode = InventoryItem.ItemCode





This gives me a result like this




╔═════════════╦═════════════╦══════════╦═══════════════════╦════════╦══════════════╦═════════════╗
║ Part Number ║ Description ║ In Stock ║ Warehouse ║ Status ║ Average Cost ║ Stock Value ║
╠═════════════╬═════════════╬══════════╬═══════════════════╬════════╬══════════════╬═════════════╣
║ 555 ║ FILTER ║ 0 ║ BRISBANE ║ A ║ 8.761043 ║ 0 ║
║ 555 ║ FILTER ║ 187 ║ MAIN ║ A ║ 8.761043 ║ 1638.315041 ║
║ 555 ║ FILTER ║ 0 ║ MELBOURNE ║ A ║ 8.761043 ║ 0 ║
║ 555 ║ FILTER ║ 21 ║ PERTH ║ A ║ 8.761043 ║ 183.981903 ║
║ 555 ║ FILTER ║ 0 ║ PATTISONS ║ A ║ 8.761043 ║ 0 ║
║ 555 ║ FILTER ║ 12 ║ QLD Warehouse (1) ║ A ║ 8.761043 ║ 105.132516 ║
║ 555 ║ FILTER ║ 22 ║ SYDNEY ║ A ║ 8.761043 ║ 192.742946 ║
╚═════════════╩═════════════╩══════════╩═══════════════════╩════════╩══════════════╩═════════════╝





However, I'm trying to write a query that will give me the TOTAL for each part number, as follows (obviously the warehouse code becomes redundant if I'm showing total for all warehouses)

This query groups by the part number.

╔═════════════╦════════════════╦══════════╦════════╦══════════════╦═════════════╗
║ Part Number ║ Description ║ In Stock ║ Status ║ Average Cost ║ Stock Value ║
╠═════════════╬════════════════╬══════════╬════════╬══════════════╬═════════════╣
║ 555 ║ WIX AIR FILTER ║ 242 ║ A ║ 8.761043 ║ 2120.172406 ║
╚═════════════╩════════════════╩══════════╩════════╩══════════════╩═════════════╝


The only query I've been able to get to work is this

SELECT
InventoryItem.ItemName as 'Part Number',
InventoryItem
SUM(InventoryStockTotal.UnitsInStock) as 'In Stock',
AVG(InventoryItem.AverageCost) AS 'Average Cost',
(AVG(InventoryItem.AverageCost) * SUM(InventoryStockTotal.UnitsInStock)) AS 'Stock Value'

FROM dbo.InventoryItemDescription
INNER JOIN dbo.InventoryItem
ON InventoryItemDescription.ItemCode = InventoryItem.ItemCode
INNER JOIN dbo.InventoryStockTotal
ON InventoryStockTotal.ItemCode = InventoryItem.ItemCode

GROUP BY InventoryItem.ItemName


Which gives me this

╔═════════════╦════════════╦══════════════╦═════════════╗
║ Part Number ║ In Stock ║ Average Cost ║ Stock Value ║
╠═════════════╬════════════╬══════════════╬═════════════╣
║ 555 ║ 242.000000 ║ 8.761043 ║ 2120.172406 ║
╚═════════════╩════════════╩══════════════╩═════════════╝


THE PROBLEM

I need to include Item Description and Status code etc in the results table as well, except when I try and add them to the select statement it returns an error

Column 'dbo.InventoryItemDescription.ItemDescription' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. I understand the cause of this error because its trying to group a column that doesn't have an aggregate, but how can I get around this?


THE IDEAL SOLUTION

The description for the part numbers will be the same for every instance, is there some way i can instruct SQL to select only the first instance of the Description?

** EDIT 2 **

Could I possibly utilise the
SELECT DISTINCT
function?

qxg qxg
Answer

If description is always the same for each part number, don't bother adding it to group by clause.

SELECT
  InventoryItem.ItemName as 'Part Number',
  InventoryItemDescription.ItemDescription as 'Description',
  SUM(InventoryStockTotal.UnitsInStock) as 'In Stock',
  FROM ...
  GROUP BY InventoryItem.ItemName, InventoryItemDescription.ItemDescription

You can use SELECT DISTINCT, which is the same as GROUP BY.

Or you can leverage window function to calculate avg/total while still keeping every description or warehouse value.

WITH CTE AS 
(
  SELECT
      InventoryItem.ItemName as 'Part Number',
      InventoryItemDescription.ItemDescription as 'Description',
      InventoryStockTotal.WarehouseCode AS 'Warehouse',
      InventoryItem.Status,
      SUM(InventoryStockTotal.UnitsInStock) OVER (PARTITION BY InventoryItem.ItemName) as 'In Stock',
      ROW_NUMBER() OVER() AS RowNumber
      FROM 
      ....
      // NO GROUP BY
)
SELECT colums
FROM CTE
WHERE RowNumber = 1
Comments