CODEWITHSUNDEEP
mysqlsqlgroup-bycounthaving
chiperortiz
chiperortiz

Reputation: 4991

MySQL group by count(1) with having by

I am brand and new with my current job i have see the following SQL sentence.

SELECT t.name store,p.id, p.name,count(1)
FROM regular_shippings sh
JOIN
   products p ON matching columns.
ANOTHER JOINS
WHERE criteria
AND anotherCriteria
GROUP BY p.id
HAVING count(1) > 0
ORDER BY t.name,count(1) desc;

My first impression is counting the records in 

the select

also in the 

having by

also in the 

order by

Impacting performance for me is better use a alias. But is this assertion correct? or is not is doing the job only once? or the engine is smart enough to recognize it and just replace the previous values of the count(1) in the latter calls?

What i mean this query would perform better?

SELECT t.name store,p.id, p.name,count(1)c
FROM regular_shippings sh
JOIN
   products p ON matching columns.
ANOTHER JOINS
WHERE criteria
AND anotherCriteria
GROUP BY p.id
HAVING c>0
ORDER BY t.name,c desc;

Sorry if the question is plain!

Upvotes: 1

Views: 1112

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

COUNT(1) is a result from the aggregation (GROUP BY). It is used in

  • the SELECT clause to be displayed,
  • in the HAVING clause to limit results and
  • in the ORDER BY clause for sorting.

It's not three different counts taking place. So it doesn't matter whether you are giving it an alias or not.

(On a sidenote: Most DBMS don't even allow the alias name given in the SELECT clause to be used in HAVING, as HAVING is supposed to occur before SELECT.)

Upvotes: 1

Related Questions