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

0
votes

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?

2
sql-serversql-server-2008
len(convert(int,min_col1_value)) doesn't make much sense. what does your desired result look like? - Zohar Peled
I should get the count as 3 (for min) and count as 4(for max). Basically wanted to know how many times minimum length of character is getting repeated. and, how many times maximum length of character is getting repeated in the column. - AskMe

2 Answers

1
votes

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.

1
votes

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
)