Problem of max(count(*))

Can someone tell me why the following sql statement cannot retrieve the
maximum count of the record:

SELECT col, max(count(*)) FROM table
GROUP BY col

And also, can I use one sql statement to retrieve the maximum count of the
record? If not, how?

Thank you more!!Hello,

you use two group function MAX and COUNT in one statement, but there is only on group by function. So the error will be "no group ..."

The count is already the maximum in the group col, but if you need the
maximum of all col the use

SELECT max(count(*)) FROM table

Hope that helps

Manfred Peter
Alligator Company Software GmbH
http://www.alligatorsql.com|||i dont know what you exactly want to retrive using max(count(*))

actually count(*) will return only one value which is maximum.

so i think the following should work for u

SELECT col, count(*) FROM table
GROUP BY col|||hi,

the only way max(count()) will have any meaning, is by getting more then 1 row from count()....
Only that case the max function will have any relevance.

example:
select count(*) from emp return one row. This is always the max
select count(*), deptno from emp group by deptno will return 3 rows. Now I have a situation in which a max would have relevance.

Hope this helps|||SELECT max(count(*)) FROM table
GROUP BY col
/*that's all folks!*/

Problem of calculation COUNT in Crystal Report

The problem is:

There is a table in SQl and has 3 columns eg Col 1, Col2, Col3.
& they have following entries.

Col1 Col2 Col3
11 12 A
33 32 B
10 10 B
40 50 A
Now i want the count of the tabel where Col3 is B.
The result should return me 2
It should be displayed under one heading.

Also i want the count of the tabel where Col3 is A
The result should return me 2
It should be displayed under another heading.

I have done it using a command and Stored Procedure.
But i dont want to do with them.

I want a formula to be used in crystal report (Formula editor/ SQL Expression)

Please, if any1 can help me outhai,
Use Running Total fields, give the conditions in the Running Total Field Formulas. hope this will works you better. let me know the status.

regards,
Bhuvana