Sorting based on count

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John - 10 Sep 2007 22:42 GMT

Hi

I have a staff required table as below;

StaffType Qty Rate
a 3 $60
b 4 $30
c 3 $30
d 4 $50
e 2 $60

I need to output this table as a select query sorted by rate but sorting is
based on how many times a rate has occurred e.g in the above data the
records b, c will come first as their rate $30 occurs most in total (7
times), then comes a and e as their rate $60 occurs 5 times and then $50
(occurs 4 times). What would be the syntax of such a select query?

Thanks

Regards

Reply to this Message

KARL DEWEY - 10 Sep 2007 23:42 GMT

Try using these two queries, substituting your table and query names ---
John_1 ---
SELECT John.Rate, Count(John.Rate) AS CountOfRate
FROM John
GROUP BY John.Rate;

SELECT John.StaffType, John.Qty, John.Rate
FROM John INNER JOIN John_1 ON John.Rate = John_1.Rate
ORDER BY John_1.CountOfRate DESC;

Signature

KARL DEWEY
Build a little - Test a little

> Hi
>
[quoted text clipped - 16 lines]
>
> Regards

Reply to this Message

Jamie Collins - 11 Sep 2007 15:27 GMT

On 10 Sep, 23:42, KARL DEWEY <KARLDE...@discussions.microsoft.com>
wrote:

> Try using these two queries, substituting your table and query names ---
> John_1 ---
[quoted text clipped - 5 lines]
> FROM John INNER JOIN John_1 ON John.Rate = John_1.Rate
> ORDER BY John_1.CountOfRate DESC;

Simpler:

SELECT John.StaffType, John.Qty, John.Rate
FROM John
INNER JOIN John AS John_1
ON John.Rate = John_1.Rate
GROUP BY John.StaffType, John.Qty, John.Rate
ORDER BY COUNT(*) DESC;

Jamie.

--

Reply to this Message