SQL ORDER BY total within GROUP BY

Question

I heed some help constructing a SQL statment. Currently I have the following:

SELECT a, b, count(1)
 FROM table
GROUP BY a, b
ORDER BY a asc, count(1) DESC

Each row displays the sum of all the unique B's within each grouping a and orders by A alphabetically and then by highest occurrence of B to the lowest. What I would actually like is to be able to sort by the total within each grouping of A (consider every row as a subtotal).

Answer 1

SELECT  a, b, COUNT(*) AS bcnt,
        (
        SELECT  COUNT(*)
        FROM    mytable mi
        WHERE   mi.a = mo.a
        ) AS acnt
FROM    mytable mo
GROUP BY
        a, b
ORDER BY
        acnt DESC, bcnt DESC