Reference an ALIAS in a SUM funciton - SQL Server

Question

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;

Just join to #tempMarketing and use conditional aggregation and avoid the subquery all together - see example below — Daniel Marcus
– Daniel Marcus, Commented Apr 18, 2018 at 16:24

Daniel Marcus · Accepted Answer · 2018-04-18 16:55:05Z

2

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;

edited Apr 18, 2018 at 16:55

answered Apr 18, 2018 at 16:27

Daniel Marcus

2,6841 gold badge10 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

S3S Over a year ago

I've never seen case when a.custid=b.custid then cogs else 0 written like this. Why not use ISNULL()? Also, while this may work, I'd explicitly alias all the columns in the select lists as we can't be certain what columns are in both tables, which would cause an error.

Aura · Accepted Answer · 2018-04-18 16:27:44Z

1

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;

answered Apr 18, 2018 at 16:27

Aura

1,3032 gold badges18 silver badges31 bronze badges

Collectives™ on Stack Overflow

Reference an ALIAS in a SUM funciton - SQL Server

Background:

Query:

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Background:

Query:

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related