Sorting based on count

[John]

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

 

[Guest]

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;

 

[Jamie Collins]

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;

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.

--