Hello Gurus,

I'm trying to get the locationaveragecost & lastpurchaseprice for each item's location in this SuiteQL query. For example, this can be easily done in the Saved Search like this:

 search.createColumn({
         name: "locationaveragecost",
         join: "item",
         summary: "MAX",
         label: "Location Average Cost"
      }),
      search.createColumn({
         name: "lastpurchaseprice",
         join: "item",
         summary: "MAX",
         label: "Last Purchase Price"
      })

But I'm unable to do it in the SuiteQL query, here is my SuiteQL Query in which I want to add locationaveragecost & lastpurchaseprice :

SELECT 
  Transaction.ID, 
  Item.ItemId, 
  TransactionLine.Location, 
  SUM(TransactionLine.Quantity) AS quantity, 
  SUM(TransactionAccountingLine.Amount) as amount, 
  Item.custitem_commodity_code, 
  Item.stockunit, 
  TransactionAccountingLine.accountingbook,
FROM 
  Transaction 
  INNER JOIN TransactionAccountingLine ON (
    TransactionAccountingLine.Transaction = Transaction.ID
  ) 
  LEFT OUTER JOIN TransactionLine ON (
    TransactionLine.Transaction = TransactionAccountingLine.Transaction
  ) 
  AND (
    TransactionLine.ID = TransactionAccountingLine.TransactionLine
  ) 
  INNER JOIN Item ON (TransactionLine.Item = Item.ID) 
  INNER JOIN InventoryItemLocations ON (TransactionLine.Item = InventoryItemLocations.Item)
WHERE 
  TransactionLine.Subsidiary = 5 
  AND Transaction.Posting = 'T'
GROUP BY Transaction.ID, Item.ItemId, TransactionLine.Location, Item.custitem_commodity_code, Item.stockunit, TransactionAccountingLine.accountingbook