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

Hello,

Hoping someone can help me figure out the proper way to get the results I am looking for. This table is the result of a daily snapshot of records, basically an inventory with costs.  So most of the records are repeated daily, but some are not. Instead of just using SUM(Cost1 * Day(EOMONTH(GetDate()))) I need to calculate the SUM of Cost1 for each Group of ActivityCodes within the table for the month based on how many times the ActivityCode exists over the course of any given month.

SELECT
Activity1, SUM((Cost1) * (SELECT COUNT(Cost1) FROM [SeedTracker].[dbo].[DBLogResInt] WHERE Activity1=Activity1)) AS Cost
FROM [SeedTracker].[dbo].[DBLogResInt]
WHERE YEAR(TimeStamp) = '2015' AND MONTH(TimeStamp) = '4'
GROUP BY Activity1
ORDER BY Activity1
Table
SELECT
Activity1, SUM(Cost1) AS Cost
FROM [SeedTracker].[dbo].[DBLogResInt]
WHERE YEAR(TimeStamp) = '2015' AND MONTH(TimeStamp) = '4'
GROUP BY Activity1
ORDER BY Activity1
Results
AhelblingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
dsackerContract ERP Admin/ConsultantCommented:
If you are summing by group, and you need to know "how many times the ActivityCode exists over the course of any given month," here is a query break out both the SUM and COUNTs by month:
SELECT  CONVERT(varchar(6), TimeStamp, 112) AS YearMth,
        Activity1,
        COUNT(1)    AS ActCnt,
        SUM(Cost1)  AS Cost
FROM    SeedTracker.dbo.DBLogResInt
WHERE   TimeStamp >= '2015-01-01'
GROUP BY CONVERT(varchar(6), TimeStamp, 112),
         Activity1
ORDER BY YearMth, Activity1

Open in new window

The COUNT(1) will tell you how many times the Activity1 exists over the course of a month. I'm not sure if you want to do any calculations with that, so let me know if this is in a direction you're looking for.
0
 
AhelblingAuthor Commented:
Yes, pretty close.
Looking to multiply SUM(Cost1) by ActCnt AS Cost

SELECT  Activity1,
        COUNT(1)    AS ActCnt,
        SUM(Cost1 * ActCnt)  AS Cost
FROM    SeedTracker.dbo.DBLogResInt
GROUP BY Activity1
ORDER BY Activity1
0
 
dsackerContract ERP Admin/ConsultantCommented:
That would be done as follows:
SELECT  CONVERT(varchar(6), TimeStamp, 112) AS YearMth,
        Activity1,
        COUNT(1)    AS ActCnt,      -- Showing temporarily, remove later
        SUM(Cost1)  AS Cost,        -- Showing temporarily, remove later
        SUM(Cost1) * COUNT(1) AS ExtCost
FROM    SeedTracker.dbo.DBLogResInt
WHERE   TimeStamp >= '2015-01-01'
GROUP BY CONVERT(varchar(6), TimeStamp, 112),
         Activity1
ORDER BY YearMth, Activity1

Open in new window

This takes the SUM of your costs, then multiplies it by the number of times that Activity1 happened in a month.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
AhelblingAuthor Commented:
That's a winner!  Thank you!
0
 
dsackerContract ERP Admin/ConsultantCommented:
Glad that helped. All the best.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.