CODEWITHSUNDEEP
sqlsql-serversumalias
TGills
TGills

Reputation: 126

Reference an ALIAS in a SUM funciton - SQL Server

Background:

I am trying to calculate the profit margin inside of the query and I am running into errors. When I try to use the select statement in the SUM function, I trigger an error:

Cannot perform an aggregate function on an expression containing an
aggregate or a subquery.

I understand that this is caused by having a SELECT query inside of the SUM function. From there, I tried to reference the alias of the COGS column. I recieve an error when I do that as well:

Invalid column name 'COGS'.

After messing around with the query some more, I figured it might be due to fact that I'm trying all of this inside of a SUM function and so I removed that and ran the query. It returned a few errors:

  • Column 'tbl_invoice.subTotal' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
  • Column 'tbl_invoice.tradeinAmount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
  • Column 'tbl_invoice.subTotal' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
  • Column 'tbl_invoice.tradeinAmount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
  • Column 'tbl_invoice.subTotal' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
  • Column 'tbl_invoice.tradeinAmount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Is there another way to use or reference the value I need in the SUM function?

Query:

--Main query
SELECT 
custID,
COUNT(custID) AS InvoiceNum,

--This is the column that has an alias
(SELECT cogs FROM #tempMarketing where #tempMarketing.custID = tbl_invoice.custID) as COGS,
--This is where I am trying to calculate the profit margin
SUM(((((subTotal + (-1 * tradeinAmount) - (SELECT cogs FROM #tempMarketing where #tempMarketing.custID = tbl_invoice.custID))) 
/ (NULLIF(subTotal + (-1 * tradeinAmount),0))) *100)) as Profitmargin, 

FROM tbl_invoice 
group by custID
order by InvoiceNum desc;

Upvotes: 0

Views: 438

Answers (2)

Daniel Marcus
Daniel Marcus

Reputation: 2686

SELECT 
a.custID,
COUNT(a.custID) AS InvoiceNum,  case when a.custid=b.custid then b.cogs else 0 end 
 as COGS,
SUM(((((subTotal + (-1 * tradeinAmount) - case when a.custid=b.custid then b.cogs else 0 end)) 
/ (NULLIF(subTotal + (-1 * tradeinAmount),0))) *100)) as Profitmargin, 

FROM tbl_invoice a 
left join #tempMarketing b on a.custID =b.custid
group by a.custID, 
case when a.custid=b.custid then b.cogs else null end 
order by InvoiceNum desc;

Upvotes: 2

Aura
Aura

Reputation: 1307

You can try the following query, I have created a common table expression for cogs column:

WITH cte_base AS(
SELECT cogs FROM #tempMarketing where #tempMarketing.custID = tbl_invoice.custID
)
SELECT 
custID,
COUNT(custID) AS InvoiceNum,

--This is the column that has an alias
cte_base.cogs as COGS,
--This is where I am trying to calculate the profit margin
SUM(((((subTotal + (-1 * tradeinAmount) - (cte_base.cogs))) 
/ (NULLIF(subTotal + (-1 * tradeinAmount),0))) *100)) as Profitmargin, 

FROM tbl_invoice 
group by custID
order by InvoiceNum desc;

Upvotes: 1

Related Questions