select max(count of rows) (was "SQL")

process · #1 (**permalink**) 01-27-05, 05:20

Hi there,

I have a problem in extracting data from db. My db is user_table with parameters like code1, code2, number_1, number_2, date_1 and date_2.

I want to extract the number of users to code1 and code2.

My coding are:

SQL> select code1, count(code1) from user_table group by code1;

Result is:

CODE1 COUNT(CODE1)
------------ ----------------
1234567890 5
0987654321 2
1472583690 6
3692581470 1

SQL> select max(code1) from user_table;

MAX(CODE1)
------------
1472583690

When i did a max(code1), instead of giving me the maximum count(code1) which is 6, code1 - 3692581470 is given.

Please advice.

Thanks a million.

Regards,

andrewst · #2 (**permalink**) 01-27-05, 07:33

You don't want the max(code1), you want the max(count of rows per code1):

Code:

select max(cnt) from
( select code1, count(code1) cnt
  from user_table
  group by code1
);

process · #3 (**permalink**) 01-28-05, 01:10

Quote:

Originally Posted by andrewst

You don't want the max(code1), you want the max(count of rows per code1):

Code:

select max(cnt) from
( select code1, count(code1) cnt
  from user_table
  group by code1
);

Hi andrewst,

Thank you for the reply. May i know what does max(cnt) means?

Regards,

andrewst · #4 (**permalink**) 01-28-05, 04:09

"cnt" is the name of a column in the inline view:

select max(cnt) from
( select code1, count(code1) cnt
from user_table
group by code1
);

process · #5 (**permalink**) 01-28-05, 05:02

Quote:

Originally Posted by andrewst

"cnt" is the name of a column in the inline view:

select max(cnt) from
( select code1, count(code1) cnt
from user_table
group by code1
);

Thank you very much.

process · #6 (**permalink**) 01-28-05, 05:21

Hi andrewst,

I have another doubt. I would like to display both code1 and the maximum count at the same time. But i encounted this error prompt.

select code1, max(cnt) from (select code1, count(code1) cnt from user_table group by code1)
*
ERROR at line 1:
ORA-00937: not a single-group group function

Thank you.

andrewst · #7 (**permalink**) 01-28-05, 05:32

That's a little harder:

Code:

select code1, count(code1)
from user_table
group by code1
having count(code1) =
( select max(cnt) from
  ( select code1, count(code1) cnt
    from user_table
    group by code1
  )
);

Or (since I believe you are using Oracle) you could use Oracle's RANK analytic function:

Code:

select code1, cnt from
( select code1, cnt, rank() over (order by cnt desc) rnk
  from
  ( select code1, count(code1) cnt
    from user_table
    group by code1
  )
)
where rnk=1;

process · #8 (**permalink**) 01-28-05, 05:51

Quote:

Originally Posted by andrewst

That's a little harder:

Code:

select code1, count(code1)
from user_table
group by code1
having count(code1) =
( select max(cnt) from
  ( select code1, count(code1) cnt
    from user_table
    group by code1
  )
);

Or (since I believe you are using Oracle) you could use Oracle's RANK analytic function:

Code:

select code1, cnt from
( select code1, cnt, rank() over (order by cnt desc) rnk
  from
  ( select code1, count(code1) cnt
    from user_table
    group by code1
  )
)
where rnk=1;

Wow.

That's pretty tough.

andrewst, you are a great help. I am able to get it.
Thank you so much.

process · #9 (**permalink**) 02-14-05, 20:52

Hi once again,

I tried this code and it works fine. However, there is a slight problem. The maximum count of the code is empty (majority of the figure goes to this empty field).

I would like to choose the 2nd maximum count which has a valid code number. Is there a possible way?

Code
====
select code1, count(code1)
from user_table
group by code1
having count(code1) =
( select max(cnt) from
( select code1, count(code1) cnt
from user_table
group by code1
)
);

Result
=====
CODE1 COUNT(CODE1)
------ ---------------
(*empty*) 63232

Or shall i include a { where code1 != ' ' } in the code as above.

Please advice.

Thank you very much.

andrewst · #10 (**permalink**) 02-15-05, 05:20

Use: "where code1 is not null"

process · #11 (**permalink**) 02-15-05, 21:44

Quote:

Originally Posted by andrewst

Use: "where code1 is not null"

Hi andrewst,

1st Doubt,

I still encounter the same result irregardless whether i enter "where code1 is not null".

SQL> select code1, count(code1) from subs_tbl where code1 is not null group by code1 having count(code1) = (select max(cnt) from (select code1, count(code1) cnt from users_table group by code1));

CODE1 COUNT(CODE1)
------- --------------
*empty* 65566

2nd Doubt,

Is there any possiblility where i can extract the 2nd maximum CODE1 and COUNT(CODE1).

Thank you so much.

Regards,

andrewst · #12 (**permalink**) 02-16-05, 05:28

OK, so it isn't a NULL in there then. That's nasty. You need to find out what is in there and then exclude that. e.g. if it is a single space then

where code1 != ' '

Sounds like your data needs cleaning up though: a "code" column should never be set to a value that is invisible (other than null of course)!

jay82 · #13 (**permalink**) 02-23-05, 06:42

Quote:

Originally Posted by andrewst

That's a little harder:

Code:

select code1, count(code1)
from user_table
group by code1
having count(code1) =
( select max(cnt) from
  ( select code1, count(code1) cnt
    from user_table
    group by code1
  )
);

Or (since I believe you are using Oracle) you could use Oracle's RANK analytic function:

Code:

select code1, cnt from
( select code1, cnt, rank() over (order by cnt desc) rnk
  from
  ( select code1, count(code1) cnt
    from user_table
    group by code1
  )
)
where rnk=1;

Hi,

Why can't we do this way??

SELECT TOP 1 code1, COUNT(code1) AS total_count
FROM user_table
GROUP BY code1
ORDER BY total_count DESC