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

jrockfl
Posting Yak Master

223 Posts

Posted - 2007-08-14 : 21:04:46

I'm trying to pull the item numbers from a lookup table, instead of hard coding them. I get this "Cannot perform an aggregate function on an express" What options do I have?

SELECT ItemNo FROM OR_InstantGifts

SELECT SUM(CASE
WHEN OEHDRHST_SQL.Orig_Ord_Type = 'O'
THEN Tot_Sls_Amt
ELSE 0
END) OrderTotal,
SUM(CASE
WHEN OEHDRHST_SQL.Orig_Ord_Type = 'C'
THEN -1 * Tot_Sls_Amt
ELSE 0
END) Credits,
OEHDRHST_SQL.Cus_No,
FLOOR(SUM(Tot_Sls_Amt) / 400) * 10 As PointsEarned,
SUM(CASE WHEN OELINHST_SQL.Item_No IN ('84361', '810691', '78265', '10006') -- Make dynamic instead
THEN 10
ELSE 0
END) PointsDeducted
FROM MACOLA.data.dbo.[OEHDRHST_SQL] OEHDRHST_SQL
INNER JOIN MACOLA.data.dbo.[OELINHST_SQL] OELINHST_SQL ON OEHDRHST_SQL.Ord_No = OELINHST_SQL.Ord_No
WHERE OEHDRHST_SQL.Cus_No IN (SELECT Cus_No
FROM MACOLA.data.dbo.[tblPoints] tblPoints
WHERE ma = 0)
AND OEHDRHST_SQL.Ord_Dt >= @FromDate
AND OEHDRHST_SQL.Ord_Dt <= @ToDate
GROUP BY OEHDRHST_SQL.Cus_No

mattyblah
Starting Member

49 Posts

Posted - 2007-08-14 : 21:58:49

[code]
FLOOR(SUM(Tot_Sls_Amt) / 400) * 10 As PointsEarned,
[/code]
is the problem.

How can you apply the floor function (static) in the same query as the sum function (aggregate). If you are looking for the only the integer portion, I'd be curious to see if
[code]
cast (sum(tot_sis_amt) / 400) as int) * 10
[/code]
would work.

jrockfl
Posting Yak Master

223 Posts

Posted - 2007-08-14 : 22:09:19

thank you so much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources