mysql function do not join the equals

Question

mysql function do not join the equals

Navigation

#1 by (6 votes)

6

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 emails together, and shows how many times they appear. I need to make it just show the number of times they repeat, but without grouping the lines.

What is it like?

link

Today it looks like this:

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

I need to leave this:

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

mysql sql

asked by anonymous 08.11.2016 / 21:35

1 answer

Doubt in recursive function Anyone know how to explain the process of generating an apk in React Native

score 6 · Accepted Answer

There's more than one way.

One reasonably simple one 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 in SQL Fiddle .

Points of interest:

We call the query main of o ( outer ) and the subquery of i ), only to be able to disambiguate and make the relation of the open fields and their counts;
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 SELECT fields, but then we would have to trust that the query planner would be smart enough not to get reworking the calculation for all the repeated lines, wasting resources and knocking the performance down. With JOIN , we do each step only once, avoiding the problem.