Simple SQL operations on GROUPed query

I have this query:

SELECT foo.*, COUNT(archive.pkey) as archivedCount FROM (
    SELECT type, count(active.pkey) AS activeCount
    FROM workertype
    LEFT JOIN active USING(type)
    GROUP BY type
) AS foo
LEFT JOIN archive USING(type)
GROUP BY type;

I would like to add a column total which would be archivedCount + activeCount 开发者_运维技巧and sort by this. (Simple enough, right?) But I can't do

SELECT foo.*, COUNT(archive.pkey) as archivedCount,
archivedCount + foo.activeCount AS total FROM (
    SELECT type, count(active.pkey) AS activeCount
    FROM workertype
    LEFT JOIN active USING(type)
    GROUP BY type
) AS foo
LEFT JOIN archive USING(type)
GROUP BY type
ORDER BY total;

since archivedCount uses an aggregate function.

It's possibly worth noting that active and archive are both very large, so anything that can be done to avoid extra work for the server would be appreciated.

The following may perform better than outer joining from workertype to active and archive, given that the latter two are apparently very large:

select type, sum(active_count) active_total, sum(archive_count) archive_total
from (select type, 0 active_count, 0 archive_count from workertype UNION ALL
      select type, 1 active_count, 0 archive_count from active UNION ALL
      select type, 0 active_count, 1 archive_count from archive) sq
group by type

Use:

   SELECT wt.type, 
          COUNT(a.pkey) AS activeCount,
          COUNT(ar.pkey) as archivedCount,
          COUNT(a.pkey) + COUNT(ar.pkey) AS total
     FROM WORKERTYPE wt
LEFT JOIN ACTIVE a ON a.type = wt.type
LEFT JOIN ARTCHIVE ar ON ar.type = wt.type
 GROUP BY wt.type
 ORDER BY total

There's no need for the subquery - this can be done in a single query.

ORDER BY in standard SQL can reference column aliases, like you see in this example.

SELECT foo.*, COUNT(archive.pkey) as archivedCount,
COUNT(archive.pkey) + foo.activeCount AS total FROM (
    SELECT type, count(active.pkey) AS activeCount
    FROM workertype
    LEFT JOIN active USING(type)
    GROUP BY type
) AS foo
LEFT JOIN archive USING(type)
GROUP BY type
ORDER BY total;