Ragukumar

• Oct 10th, 2005

 

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

nileshsingh

 Profile Answers by nileshsingh 

• May 19th, 2006

 

Hi......i am not clear with the question.can any one explain it to me properly.

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

Tanik

• Apr 26th, 2007

 

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

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

ranjeetapegu

 Profile Answers by ranjeetapegu Questions by ranjeetapegu

• Jun 22nd, 2007

 

Yes we can use..
here is an example

select avg(sal) from
(select max(salary) sal,deptno from emp1
group by deptno);

select max(num_emp) from
(select count(empno) num_emp,deptno from emp1
group by deptno

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>

gkbiswal

 Profile Answers by 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

billablaze

 Profile Answers by billablaze 

• Jun 5th, 2008

 

hi .. good night ..!