Mysql function does not merge equals

Question

I have this query for Mysql:

SELECT email, COUNT(*) AS Vezes FROM automovel
GROUP BY email HAVING COUNT(*)
ORDER BY `data_cadastro` ASC

It groups all the same emails together, and shows how many times they appear. I need to make her just show how many times they repeat themselves, but without grouping the lines.

Has as?

https://fiddle.jshell.net/e70kz8fo/

Today she displays like this:

email           Vezes em que aparece
pedro1@teste    1
pedro2@teste    3

I need to leave it like this:

email           Vezes em que aparece
pedro2@teste    3
pedro1@teste    1
pedro2@teste    3
pedro2@teste    3 

Answer 1

There’s more than one way.

One of them, fairly simple, is to use JOIN:

SELECT
  o.email,
  i.vezes
FROM
  automovel o
LEFT JOIN
  ( SELECT COUNT(*) AS vezes, email FROM automovel GROUP BY email ) i
  ON i.email = o.email
ORDER BY
  data_cadastro

See working on SQL Fiddle.

Points of interest:

• We call the query principal of o (Outer) and the subquery of i (inner), only to be able to disambiguate and make the relation of the open fields and their counts;

• to query main list all items, repeated or not, without counting;

• to subquery lists only items grouped by email, and their counts;

• we relate the two sides by the field email, so that the respective count is shown in all occurrences.

It could have been done differently, with the subquery within the fields of SELECT, but then we would have to trust that the query Planner You’d be smart enough not to keep recalling all the repeat lines, wasting resources and knocking down performance. With the JOIN, we do each step once, avoiding the problem.