How could I aggregate a description column?

Question

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?

If description is always the same for each part number, don't bother adding it to group by clause. Also you can leverage `window` function to calculate avg/total while still keeping every description or warehouse value. — qxg, Apr 21 '16 at 05:12
Hi, I didnt add it to the Group By Clause.... unless I did!!! How would I not include this in the group by clause? — Brendan Gooden, Apr 21 '16 at 05:17

score 3 · Accepted Answer · answered Apr 21 '16 at 05:20

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

Cheers, that's exactly what I need! The first code example does it. Can you explain why adding the column after the `GROUP BY` clause does NOT include it? — Brendan Gooden, Apr 21 '16 at 05:30
Check out this [article](http://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/) — qxg, Apr 21 '16 at 05:32

How could I aggregate a description column?

1 Answers1