Получение поля стоимости из подзапроса

У вас есть пара проблем.

В подзапросе вы используете псевдоним c для COSReport. Вы также используете псевдоним c для customers во внешнем запросе.

Более серьезная проблема заключается в том, что ваш подзапрос вообще не коррелирует с вашим внешним запросом. Вот почему он суммирует всю таблицу.

Чтобы сопоставить ваш подзапрос, вам необходимо присоединиться (в подзапросе) к одной из таблиц во внешнем запросе. Не уверен в ваших таблицах или данных, но предполагаю, что вы хотите использовать ns.tinvoice i в предложении WHERE в своем подзапросе.

На самом деле я предпочитаю использовать CTE для удобства чтения. Вы можете взять свой подзапрос, поместить его в CTE, а затем присоединить к нему в своем основном запросе, но вы захотите добавить поля tranid и item_id в CTE, чтобы вы могли использовать их в своем объединении.

Обновлено: поскольку вы используете Azure SQL Server, вам не нужна точка с запятой перед WITH.

WITH TrueCosts AS
(
    SELECT
        d.tranid 
        ,d.item_id
        ,TrueCost = SUM(c.tranamt) 
    FROM ns.COSreport c
    INNER JOIN ns.ProfitabilityReport d
        ON c.InvoiceID = d.tranid 
            AND c.item_id = d.item_id
    GROUP BY d.tranid 
        ,d.item_id
)
SELECT 
    i.tranid
    , it.item_id
    , it.displayname
    , tl.Item_Count * -1 Unit_Qty
    , case when tl.Item_Count=0 then 0 
             else ((tl.GROSS_AMOUNT * -1)/ Item_Count) * -1 
        END as PricePerUnit
    , tc.TrueCost AS 'True Cost'
    , '0' AS 'Cost Per M'
FROM ns.tinvoice i
JOIN ns.transaction_lines tl on i.transaction_id = tl.transaction_id
JOIN ns.Customers c on c.customer_id = i.ENTITY_ID
JOIN ns.items it on it.item_id = tl.item_id
LEFT JOIN ns.ITEM_CLASSIFICATION it_class on it_class.list_id = it.ITEM_CLASSIFICATION_ID
LEFT JOIN TrueCosts tc ON tc.tranid = i.tranid AND tc.item_id = tl.item_id
WHERE list_item_name IS NOT NULL
    AND i.tranid = '1262INV'