SELECT inside a COUNT

You don’t really need a sub-select:

SELECT a, COUNT(*) AS b,
   SUM( CASE WHEN c="const" THEN 1 ELSE 0 END ) as d,
   from t group by a order by b desc