.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

SQL PROCEDURE cannot perform aggregate function on expression containing aggregate or subquery

Posted By:      Posted Date: October 27, 2010    Points: 0   Category :Sql Server


I DID BELLOW CODE BUT GETTING ERROR AS cannot perform aggregate function on expression containing aggregate or subquery



ALTER PROCEDURE ShowcClientvsTimevsCost(@Country nvarchar(15) )

group by ACTIVITY

View Complete Post

More Related Resource Links

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


How do i get around this???



sum(PODet.UnitCost*ReceiverDet.Qty2Receive) as TotalCost


About the Aggregate Function MIN

Hi all, I am using the select statement with MAX(id), MIN(id), COUNT(*) of a very big table and it is returning me the value in less than a second. But surprisingly if i am using aggregate function MIN(id) in a seperate select statement it is taking upto 90 Seconds. Here are the 2 statements I am running SELECT MAX(ID),MIN(ID),COUNT(*) FROM A TABLE  -- Time consuming for this:  less than a Second. SELECT MIN(ID)FROM A TABLE  -- Time consuming for this:  Upto 90 Seconds. Please clarify how it work internally when we call the select statement with Aggregate functions?  chinna

Writing a custom Aggregate function in SSIS


Hi is it possible to write a custom aggregate function in SSIS. Is there any other way to do it better? Any help is highly appreciated.


My values are like this

Item | VALUE








I am trying to bring the output. 


1, abc-xyz-wer

2, def-tyu

3, ppl

Ganesh Ranganathan
[Please mark the post as answer if it answers your question]

Cannot find either Column "dbo" or the user-defined function or aggregate "dbo.GetCandiateID", or th



Good morning to everybody. I am not understanding why I am getting the following error (also mentioned in the subject line).

I have written the following function in SQL Server 2005:

    declare @candidate_id int
    SELECT @candidate_id = max(c.candidate_id) from dbo.candidates c
    if(@candidate_id is null)
        set @candidate_id = 1001
        set @candidate_id = @candidate_id + 1
    return @candidate_id

The function compiled properly. I have used the above function like below in the query:

select dbo.GetCandidateID()

I am getting the following error:

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.GetCandidateID", or the name is ambiguous

can i create a Clr ud Aggregate Function with over/ order by clause?


i want create a UD Aggregate like  Row_Number () for using with 'Over (Order By ...) ' (not like sum() over (part....)

is it possible?

SSRS aggregate function


I have an SSAS MDX query that uses a calculated member;  when adding the subtotal in the report I would like to rely on SSAS doing to aggregation rather than the incorrect sum SSRS is trying to do.  But when I use =Aggregate(Fields!x.Value)  I dont get anything back, the cell is blank.


Javier Guillen

Aggregate (count) function that produces a different result to what is expected.

Hi Folks
I have an aggregate transformation, that runs a count on a column.  The  column  has 23 rows  with dates and a further 2 rows that have no date information. When I run a count function on the column, it returns a count of 25.  My hypothesis is that  this is linked to the 2 rows are treated as being  BLANK as opposed to being NULL (I understand the COUNT function will not count rows that have null values).

I have two questions
1) How do test this hypothesis that he difference is due to a blank vs null issue and then more importantly
2) How to I resolve the issue so that the 2 "empty" rows are not counted?

Many thanks


Userdefined aggregate function erroring out when all values are null


HI, I have a aggregate function that calculates median in all cases excet if every value that is being passed in is null and I cant seem on how to fix it. I beleive it has to do with either the read or write methods below is the code that I am using and the error. Any help on this would be greatly appreciated. Thanks in advane.








Built-in Functions - Aggregate Functions

Aggregate functions return a single value summarizing a given data set. All aggregate functions are deterministic. NOTE: AVG, SUM, STDEV, STDEVP, VAR and VARP functions cannot operate on BIT data types; they can operate on all other numeric data types.

Creating .NET Assemblies That Aggregate Data from Multiple External Systems for Business Connectivit

This article describes a quick, four-step process for creating a .NET assembly that BCS can use to retrieve external data for SharePoint Server 2010 by using Visual Studio 2010.

Dynamic Dimension with Aggregate Values

Hi, I have an specific requirement to make the measure value as an dimension. Let me explain my problem in brief. I have a fact table with dimensions like Time, Products etc and having single fact table with two measures. I have to create a calculated measure which shows the average of Measure 1 (here used calculated measure because there are couple of other calculations involved). And other two calculated measures. when I drill down with Products dimension for Calculate measure 1, it shows the average value for each products. Now, I want this calculated measure values (includes Product dimesnion drill down) as a Dimension and based on this value, I need to show the value of other two measures. For example: when the dimension products is used for drill down the values displayed will be like this and in this I need CM1 to be another dimension Products CM1 CM2 CM3 P1 0.10% 20 1 P2 0.20% 40 2 P3 0.30% 80 3 P4 0.40% 70 4 P5 0.50% 30 5 P6 0.60% 110 6 P7 0.70% 120 7 P8 0.80% 130 8 P9 0.90% 86 9 P10 1.00% 65 10 when CM1 is used as a dimension it should show the value like this CM1 CM2 CM3 0.10% 20 1 0.20% 40 2 0.30% 80 3 0.40% 70 4 0.50% 30 5 0.60% 110 6 0.70% 120 7 0.80% 130 8 0.90% 86 9 1.00% 65 10 How can we create the dynamic dimension with the aggregated values? Any assistance will be greatly apprec

MDX - The function expects a tuple set expression for the 1 argument

I have a  scenerio dimension with several different scenerios. I am trying  subtract the measure values of the descendants in one scenerio to it's cousin in another scenerio. Here is my MDX query: WITH SET [actual - forecast] AS GENERATE( DESCENDANTS([Scenerio Dim].[Scenerio hierarchy].[actual], 0, SELF_AND_AFTER), { ( [Scenerio Dim].[Scenerio hierarchy].CURRENTMEMBER, [Measures].CURRENTMEMBER ) - ( COUSIN([Scenerio Dim].[Scenerio hierarchy].CURRENTMEMBER, [Scenerio Dim].[Scenerio hierarchy].[forecast baseline]), [Measures].CURRENTMEMBER ) } ) SELECT [actual - forecast] ON 0 FROM [actual vs forecast] I get the following error message: Query (4, 14) The function expects a tuple set expression for the 1 argument. A string or numeric expression was used.

Function count through Regular Expression

Hi, I have a table with varchar(max) column. This column stores all the function calls to application. Example 1    SUM(SALARY); AVG(LEAVES)/COUNT(DAYS); 2    SUM(SALARY)/AVG(SALARY); AVG(LEAVES)/COUNT(DAYS); COUNT(EMPLOYEES); The above output shows the requests of User 1 and 2. User 1 called SUM, AVG and COUNT functions once. User 2 called SUM once and AVG, Count twice. I need to calculate these counts through Regular expression in SQL Server 2005. Following is script for table and data generation: CREATE TABLE [dbo].[UserRequests] (  [UserID] [int] NOT NULL,  [Requests] [varchar](255) NULL ) ON [PRIMARY] insert into [dbo].[UserRequests] values (1, 'SUM(SALARY); AVG(LEAVES)/COUNT(DAYS);') insert into [dbo].[UserRequests] values (2, 'SUM(SALARY)/AVG(SALARY); AVG(LEAVES)/COUNT(DAYS); COUNT(EMPLOYEES);') Any suggestion or idea would be highly admired. PS: if someone has another optimized solution for this problem, kindly suggest that as well. Regards.

Aggregate only filtered members

I’m creating a measure in a cube as lookup from another cube: CREATE MEMBER CURRENTCUBE.[MEASURES].[Test]  AS     lookupcube("[Cube1]",     "([Measures].[PublicPrice],     [Cube1 DimDate].[TIME Hierarchy].[" + [Cube2 DimDate].[TIME Hierarchy].currentmember.name + "]     )")     If I select in filter only January 2010 and February 2010 in Cube1, for Quarter 1  I obtain 100; in Cube2  I obtain 150. Even if March 2010 is not selected in Cube2 the aggregation level (the Quarter) considers it. Could someone help me? Thank you very much, Giada

What is best way to aggregate tasks in Intranet?

A lot of Intranet users are challenged to keep track of tasks across multiple team sites. Anyone find a technique that works? It seems like it would make sense to live in a user's My Site on SP2010.  Anyone doing it that way?      

Allmembers function expects a hierarchy expression for the argument, a member expression is used" er

  Hi , I have a report that was working before.  After adding couple calculated mesaure, my report is throwing the error.  Here is the query I used.  Can you please help? this was a working prototype, now it is in trouble.  Need to fix it quickly.  thanks a million   SELECT NON EMPTY { [Measures].[sales], [Measures].[%of sales] } ON COLUMNS, NON EMPTY { ([region].[country].[country].ALLMEMBERS * [Fiscal Period].[Fiscal Year].[Fiscal Year].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [sales] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS      

Stored Procedure Subquery Issue

Hi, In the below stored procedure, I often get this error message... "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." Why is this? I check for multiple entries and delete any that exist. Yet still gives that error on occassion.  Thanks. ALTER PROCEDURE [dbo].[MainTbl] -- Add the parameters for the stored procedure here @Name nvarchar(5), AS SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; Begin Transaction DECLARE @DetailsColour nvarchar(6); -- Insert statements for procedure here Begin -- Details DECLARE @CountDetails int; DECLARE @FirstEntryDetails int; SET @CountDetails = (SELECT COUNT (Name) FROM [DetailsTbl] WHERE Name = @Name ) if (@CountDetails > 1) Begin SET @FirstEntryDetails = (SELECT TOP(1) NameID FROM [DetailsTbl] WHERE Name = @Name ORDER BY NameID ASC) DELETE FROM [DetailsTbl] WHERE Name = @Name AND (NameID != @FirstEntryDetails) End Set @DetailsColour = (SELECT DetailsColour FROM [DetailsTbl] WHERE Name = @Name) if @DetailsColour is null begin set @DetailsColour = 'RED' end DECLARE @FoundData int; SET @Found
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend