CODEWITHSUNDEEP
sql-serversql-server-2008
AskMe
AskMe

Reputation: 2561

Can't perform an aggregate function on an expression containing an aggregate or a subquery

My table is created like this :

create table ##temp2(min_col1_value varchar (100))
create table ##temp1(max_col1_value varchar (100))

The table has values like this:

   min_col1_value
-------------------
       1
       0
       10
       1

I'm trying to get the "frequency count of minimum length values" and expecting result as 3.

another example for maximum is :

 max_col1_value
-------------------
       1000
       1234
       10
       1111
       123
       2345

I'm trying to get the "frequency count of maximum length values" and expecting result as 4.

When I'm running this query:

  select count(min(len(convert(int,min_col1_value)))) from ##temp2 group 
  by min_col1_value

select count(max(len(convert(int,max_col1_value)))) from ##temp1 group by 
max_col1_value

getting error as : Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

How to get the desired result?

Upvotes: 0

Views: 7694

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

This should get you your desired results:

SELECT COUNT(*)
FROM ##temp2
WHERE LEN(min_col1_value) = 
(
    SELECT MIN(LEN(min_col1_value)) 
    FROM ##temp2
)

SELECT COUNT(*)
FROM ##temp1
WHERE LEN(max_col1_value) = 
(
    SELECT MAX(LEN(max_col1_value)) 
    FROM ##temp1
)

Upvotes: 1

JNevill
JNevill

Reputation: 50034

You can't aggregate twice in the same SELECT statement and, even if you could, your min(len()) will return a single value: 2 since your minimum field length of #temp2 is 2. Counting that will just give you 1 because there is only 1 value to count.

You are wanting to get the count of how many fields have that minimum length so you'll need something like:

SELECT count(*)
FROM #temp2
WHERE len(min_col1_value) IN (SELECT min(len(min_col1_value)) FROM #temp1)

That WHERE clause says, only count values in #temp2 where the length is equal to the minimum length of all the values in #temp2. This should return 3 based on your sample data.

The same logic can be applied to either table for min or max.

Upvotes: 1

Related Questions