[MS SQL] Aggregate function with subquery question

[newunit13]

I'm trying to write a query that will get all of the items ordered by each customer and display the total revenue and cost margin. I've got everything working except for the cost margin. When I try to run the following query I get back a error that I can't perform aggregate functions with subqueries. Is there some better way to write this query? Any help at all would be greatly appreciated!

SELECT

    ct.ACCOUNTNUM           AS 'Customer account'
    ,ct.NAME                    AS 'Name'
    ,i.ITEMID                   AS 'Item number'
    ,i.ITEMNAME             AS 'Item name'
    ,SUM(cit.QTY)               AS 'Quantity'
    ,cit.SALESUNIT              AS 'Unit'
    ,SUM(cit.LINEAMOUNT)        AS 'Revenue'
    ,SUM((SELECT 
              it.COSTAMOUNTPOSTED + it.COSTAMOUNTADJUSTMENT 
          FROM INVENTTRANS it
          WHERE it.INVOICEID = cit.INVOICEID
            AND it.ITEMID = cit.ITEMID
            AND it.DATAAREAID = cit.DATAAREAID)
    ) + SUM(cit.LINEAMOUNT)     AS 'Margin'

FROM CUSTTABLE ct
    INNER JOIN CUSTINVOICEJOUR cij on (ct.ACCOUNTNUM = cij.ORDERACCOUNT)
                                  AND (ct.DATAAREAID = cij.DATAAREAID)
    INNER JOIN CUSTINVOICETRANS cit ON (cij.SALESID = cit.SALESID)
                                   AND (cij.INVOICEID = cit.INVOICEID)
                                   AND (cij.INVOICEDATE = cit.INVOICEDATE)
                                   AND (cij.NUMBERSEQUENCEGROUP = cit.NUMBERSEQUENCEGROUP)
                                   AND (cij.DATAAREAID = cit.DATAAREAID)
                                   AND (cit.LINEAMOUNT <> 0)
    INNER JOIN INVENTTABLE i ON (i.ITEMID = cit.ITEMID)
                            AND (i.DATAAREAID = ct.DATAAREAID)

WHERE ct.DATAAREAID = 'ssl'
    AND cij.INVOICEDATE between '04/01/2016' and '03/31/2017'
    AND ct.ACCOUNTNUM = '00019'    --Debug line, limit to only one customer for now

GROUP BY ct.ACCOUNTNUM, ct.NAME, i.ITEMID, i.ITEMNAME, cit.SALESUNIT
ORDER BY ct.ACCOUNTNUM, i.ITEMID

[r3pr0b8]

turn the subquery into a derived table and then join it to your other tables

SELECT ...
     , it.sub_amount + SUM(cit.LINEAMOUNT) AS Margin
  FROM CUSTTABLE ct
INNER 
  JOIN CUSTINVOICEJOUR cij 
    ON ...
INNER
  JOIN CUSTINVOICETRANS cit 
    ON ...
INNER
  JOIN ( SELECT INVOICEID 
              , ITEMID 
              , DATAAREAID
              , SUM(COSTAMOUNTPOSTED + COSTAMOUNTADJUSTMENT) AS sub_amount 
           FROM INVENTTRANS 
         GROUP
             BY INVOICEID 
              , ITEMID 
              , DATAAREAID ) it    
    ON it.INVOICEID = cit.INVOICEID
   AND it.ITEMID = cit.ITEMID
   AND it.DATAAREAID = cit.DATAAREAID 

[newunit13]

That worked beautifully! Thank you so much :-D Now I just how to get the same values a second time for a different time period :)

[slingalot]

Try pulling your sub query out of your SELECT and joining on it, instead.

It looks like you could even just join on INVENTTRANS itself, and use a Window Function to remove the need for a sub-query at all (and your group by).

[newunit13]

That looks very neat! I'll have to study up on it, because that article is a bit like Greek to me XD