Counting the maximum value

[John]

Hi

I have a column of amounts. I need to find the count of the maximum amount.
For example if I have the below records

$10
$9
$8
$9
$10
$9

then the maximum is $10 and the count is 2. How can I do it via a query?

Thanks

Regards

 

[giorgio rancati]

Hi,
try these queries

#1 Derived table
----
SELECT tab1.amount, Count(tab1.amount) AS CountOfAmount
FROM tab1,
(Select Max(amount) AS MaxOfAmount From tab1) AS DrvTbl
WHERE tab1.amount=DrvTbl.MaxOfAmount
GROUP BY tab1.amount;

 

[John W. Vinson]

Hi

I have a column of amounts. I need to find the count of the maximum amount.
For example if I have the below records

$10
$9
$8
$9
$10
$9

then the maximum is $10 and the count is 2. How can I do it via a query?

Thanks

Regards

You can do it with one subquery:

SELECT Amount, Count(*)
FROM tablename
HAVING Amount = (SELECT Max(X.[amount]) FROM tablename AS X)
GROUP BY Amount;

If you want to do this on a subset of the records rather than on the whole
table, you'll need WHERE clauses is both the main and subqueries.

John W. Vinson [MVP]

 

[Michel Walsh]

In addition to the answers you already got, you can use a TOP 1 on a group
by query, something that may possibly looks like:


SELECT TOP 1 amount
FROM tableName
GROUP BY amount
ORDER BY COUNT(*) DESC



which will return the amount having the maximum number of occurrence (if
there are two amounts occurring to the same max number of occurrence, they
will be both listed).


Note that the syntax can be a little bit deceptive. It does NOT take the
TOP 1 ***amount*** , no, it takes the top 1 record (here, group of records)
and from it, select the field amount. Which is the top 1 record, you can
ass? well, after all records are grouped and order by their number of
occurrence, the order being descending, the top one on the list is the
(group of) record you will got.



Vanderghast, Access MVP