Cannot perform an aggregate function on an express

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Cannot perform an aggregate function on an express

Author

Topic

jrosser
Starting Member

14 Posts

Posted - 2008-06-05 : 09:26:02

I am trying to calculate margin, I wrote an ASP.net application that lets users select if they want to show Sales, Cost and I want to let them be able to select Margin as well. The query below works fine with Sales or Cost but when I try to calculate Margin I get the following error:

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

--WORKS
SELECT branch_id
,sum(CASE WHEN left(pg_id,1) = 'A' THEN SALES ELSE 0 END) AS [Access]
FROM cube
GROUP BY branch_id

--DOES NOT WORK
SELECT branch_id
,sum(CASE WHEN left(pg_id,1) = 'A' THEN (sum(SALES)-sum(COST))/sum(SALES)*100 ELSE 0 END) AS [Access]
FROM cube
GROUP BY branch_id

The problem is that I need to subtract the sum of Sales from the sum of cost. I have been playing around with this one for awhile.

Thanks

Jeremy

Jeremy Rosser

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-06-05 : 09:31:42

see if this helps:

http://www.sqlteam.com/article/aggregating-correlated-sub-queries

- Jeff
http://weblogs.sqlteam.com/JeffS

jrosser
Starting Member

14 Posts

Posted - 2008-06-05 : 09:47:24

That really does not help me, I need to use the sum(case
because I am displaying the CASE as a column instead of a row.

Is there some type of nested Case I could use?

In my ASP app I am passing values such as the "A" and the value that you want to display in that Column such as Sales or Cost.

Jeremy Rosser

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-05 : 10:29:07

[code]SELECT branch_id,
SUM(CASE WHEN pg_id LIKE 'A%' THEN 100.0 * (SALES - COST) / SALES ELSE 0.0 END) AS [Access]
FROM cube
GROUP BY branch_id[/code]

E 12°55'05.25"
N 56°04'39.16"

jrosser
Starting Member

14 Posts

Posted - 2008-06-05 : 10:40:25

Peso, that looks like it will work, but now I am getting the good old divide by 0.

Divide by zero error encountered.

How would we add something like this to that query?

CASE WHEN sum(SALES) in (0,Null) then 0 else (Continue)

Thanks

Jeremy Rosser

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-05 : 10:42:26

Can you have a cost without a sale?

SELECT		branch_id,
		SUM(CASE WHEN pg_id LIKE 'A%' THEN 100.0 * (SALES - COST) / SALES ELSE 0.0 END) AS [Access]
FROM		cube
WHERE		Sales <> 0.0
GROUP BY	branch_id

E 12°55'05.25"
N 56°04'39.16"

jrosser
Starting Member

14 Posts

Posted - 2008-06-05 : 10:52:30

Nope, this will not work, the results are not correct.

I need to somehow first Sum(Sales) and Sum(Cost) then do the calculation

Any ideas?

Jeremy Rosser

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-05 : 11:06:20

Will this do?

select	branch_id,
	100.0 * (sales - cost) / nullif(sales, 0) AS [access]
from	(
		SELECT		branch_id,
				SUM(SALES) AS sales,
				SUM(COST) AS cost
		FROM		cube
		WHERE		pg_id LIKE 'A%'
		group by	branch_id
	) as d

E 12°55'05.25"
N 56°04'39.16"

jrosser
Starting Member

14 Posts

Posted - 2008-06-05 : 11:10:19

I think this might work, now I will see if I can code it into my ASP App, thanks for your help,

Jeremy Rosser

jrosser
Starting Member

14 Posts

Posted - 2008-06-06 : 12:14:54

This is still giving me some trouble, yes it seems to work as a query but it will be next to impossible to intigrate it into a ASP application. I wish there was a cleaner way.

,sum(CASE WHEN left(pg_id,1) = 'A' THEN SALES ELSE 0 END) AS [Access]

This work so well for SALES and COST, is there a way to do a formula in this format?

Jeremy Rosser

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources