CODEWITHSUNDEEP
sqlmysqlaggregate-functions
Charles
Charles

Reputation: 11479

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.

Upvotes: 0

Views: 94

Answers (3)

user359040
user359040

Reputation:

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

Upvotes: 3

mkr
mkr

Reputation: 11

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;

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332641

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.

Upvotes: 2

Related Questions