Subject Re: question about optimizing group by
Author woodsmailbox
Ok, sorry, I stripped down the query to get understood to the point
that it didn't make sense. Please let me try again.

Below are 4 equivalent selects. Question is, wouldn't the last query
perform faster than the others?

select
c.company_id,
c.company_name,
count(1) as employee_count
from
companies c
inner join company_employees ce on ce.company_id = c.company_id
group by
c.company_id,
c.company_name -- SUPERFLOUS INCLUSION OF company_name in SORT
TABLE or INDEX SCAN

ALTERNATIVE SYNTAX #1

select
c.company_id,
min(c.company_name), -- SUPERFLOUS INDEX SCAN TO COMPUTE min()
count(1) as employee_count
from
companies c
inner join company_employees ce on ce.company_id = c.company_id
group by
c.company_id

ALTERNATIVE SYNTAX #2

select
c.company_id,
c.company_name,
t.employee_count
from
companies c
inner join (select company_id, count(1) as employee_count from company_employees group by company_id)
group by
c.company_id

ALTERNATIVE SYNTAX #3 with a hypothetical first() aggregate function:

select
c.company_id,
first(c.company_name),
count(1) as employee_count
from
companies c
inner join company_employees ce on ce.company_id = c.company_id
group by
c.company_id