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

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;
----

#2 SubQuery
----
SELECT tab1.amount, Count(tab1.amount) AS CountOfAmount
FROM tab1
WHERE tab1.amount=(Select Max(amount) From tab1)
GROUP BY tab1.amount;
----

#3 Domain Aggregate function
----
SELECT tab1.amount, Count(tab1.amount) AS CountOfAmount
FROM tab1
WHERE tab1.amount=Dmax("amount","tab1")
GROUP BY tab1.amount;
----

bye
--
Giorgio Rancati
[Office Access MVP

"John" <(E-Mail Removed)> ha scritto nel messaggio
news:(E-Mail Removed)...
> 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
>
>
>

On Sun, 6 Jan 2008 08:51:22 -0000, "John" <(E-Mail Removed)> wrote:

>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]

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


"John" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
>