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

General SQL Server Forums

New to SQL Server Programming

Cannot perform an aggregate function on an express

Author

Topic

cardnal0602
Starting Member

11 Posts

Posted - 2008-10-09 : 13:42:10

Hey I am struggling with syntax to perform an aggregate function within another aggregate function. Is it possible? Any help is much appreciated. Thanks!

My code is below:

declare @FirstDay datetime
declare @LastDay datetime
set @FirstDay = '7/1/2008'
set @LastDay = '10/1/2008'

select products.drprice,
sum(((products.whprice * 1.4)-products.drprice)*count(orderdetails.quantity))
from orders, orderdetails, clinicproducts, products
where (CONVERT(DATETIME, orders.dateordered, 101) >= @firstDay and CONVERT(DATETIME, orders.dateordered, 101) <= @lastDay)
and orders.clinicdrID = 588
and orders.orderstatus = 1
and orders.deleted = 'N'
and orders.orderID = orderdetails.orderID
and orderdetails.deleted = 'N'
and orderdetails.clinicproductID = clinicproducts.clinicproductID
and clinicproducts.productID = products.productID
group by products.whPrice, products.drPrice, orderdetails.quantity

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 13:48:56

you cant do aggregate over aggregate. but you can do a workaround like below

declare @FirstDay datetime
declare @LastDay datetime
set @FirstDay = '7/1/2008'
set @LastDay = '10/1/2008'

select 
sum(((whprice * 1.4)-drprice)* orderqty))
from
(
select products.drprice, 
products.whprice,
count(orderdetails.quantity) as orderqty
from orders, orderdetails, clinicproducts, products 
where (CONVERT(DATETIME, orders.dateordered, 101) >= @firstDay and CONVERT(DATETIME, orders.dateordered, 101) <= @lastDay) 
and orders.clinicdrID = 588 
and orders.orderstatus = 1 
and orders.deleted = 'N' 
and orders.orderID = orderdetails.orderID 
and orderdetails.deleted = 'N' 
and orderdetails.clinicproductID = clinicproducts.clinicproductID 
and clinicproducts.productID = products.productID
group by products.whPrice, products.drPrice
)t

also your original contains a final sum after grouping on same fields which i think is meaningless. why do you group on a qty and them sum on it? cant understand what result you're looking at here?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-10-09 : 13:50:57

try using a drived table. like this:

DECLARE @FirstDay DATETIME
DECLARE @LastDay DATETIME
SET @FirstDay = '7/1/2008'
SET @LastDay = '10/1/2008'

SELECT 
	drprice
	SUM (Amount)
FROM
(
	SELECT
		products.drprice,
		((products.whprice * 1.4) - products.drprice) * COUNT(orderdetails.quantity) AS Amount
	FROM
		orders,
		orderdetails,
		clinicproducts,
		products
	WHERE(CONVERT(DATETIME, orders.dateordered, 101) >= @firstDay
		AND CONVERT(DATETIME, orders.dateordered, 101) <= @lastDay)
		AND orders.clinicdrID = 588
		AND orders.orderstatus = 1
		AND orders.deleted = 'N'
		AND orders.orderID = orderdetails.orderID
		AND orderdetails.deleted = 'N'
		AND orderdetails.clinicproductID = clinicproducts.clinicproductID
		AND clinicproducts.productID = products.productID
	GROUP BY
		products.whPrice,
		products.drPrice,
		orderdetails.quantity
) AS T
GRUP BY drprice

EDIT: Although slightly different that Visakh's solution, I'm slow.. :)

cardnal0602
Starting Member

11 Posts

Posted - 2008-10-10 : 18:30:44

Thanks guys!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources