Can we use aggregate function in another aggregate functionsuppose we got count of a column by using the group by clause so we get different counts in the same statement how to get the maximum of count

Showing Answers 1 - 27 of 27 Answers

Ragukumar

  • Oct 10th, 2005
 

group by ...having... clause can be used for this question

  Was this answer useful?  Yes

Renuka rajput

  • Jul 29th, 2006
 

select max(count(deptno)) from emp
group by deptno;

Laxmi Prasad

  • Apr 2nd, 2007
 

I think i shdnt workout

first it will return count then what max value will return that

  Was this answer useful?  Yes

Tanik

  • Apr 26th, 2007
 

Count function itself will gives number. so i don't think there is anything that gives max of it.

  Was this answer useful?  Yes

saritha

  • May 2nd, 2007
 

I think we can use  ROLLUP and CUBE functions in group by clause to get aggregation of aggregates...

genie_cool

  • Jun 6th, 2007
 

Hi,
      This is for SQL guys... SQL does not accept using two aggregate functions.. like MAX(COUNT(Some_Col))... This will throw error... but we can create a Temp Table and put the values into it and then get the Max out of it...

Sample Code:

--Create a table for testing
DECLARE @Tab TABLE
(
 A INT,
 B INT,
 C INT,
 D VARCHAR(10)
)

DECLARE @i INT

SET @i = 0

--Insert into the table some values
WHILE @i < = 10
 BEGIN
  INSERT INTO @Tab VALUES (@i, @i+1, @i+2, 'A')
  SET @i = @i + 1
 END

--Update the set of values under a group
UPDATE @Tab
 SET D = 'C'
 WHERE A > 5

--Contents of the Table
Select * from @Tab
--Create a Temp Table to store the Count() values
DECLARE @Cnt TABLE
(
 TotCol INT
)

INSERT INTO @Cnt
 Select COUNT(A) FROM @Tab
 GROUP BY D

--Select Maximum Count values
SELECT MAX(TotCol) FROM @Cnt


  Was this answer useful?  Yes

gajanan_kamalja

  • Jul 13th, 2007
 

There are two ways to use but only on is valid.
1. The following query explains first way in which you can use aggregate functions (sum, min, max, avg, count, etc).

Select max(cnt) from

(

Select count(*) as cnt,scab

from <tablename>

group by scab

)kk

and another way

2. this second way is wrong since we can’t use aggregate function inside another aggregate function.

select max(Count(*))

from <tablename>


  Was this answer useful?  Yes

gkbiswal

  • Sep 29th, 2007
 

We can use aggregate function inside another aggregate function.

Example:

SQL> select max(Count(*)) from emp group by deptno;

MAX(COUNT(*))
-------------
            6

DB Version: Oracle9i Enterprise Edition Release 9.0.1.1.1

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions