Combining data mysql query

I have this table: trafico.2019

(
  Id    int,
  PAÍS  varchar(255),
  FLUJO varchar(255),
  `PUERTO PENINSULAR`   varchar(255),
  `GRUPO ESTADÍSTICO`   varchar(255),
  NATURALEZA    varchar(255),
  TONELADAS double
)

Where I need to select different kinds of traffic (combinations of variables grouped in the following query) Q1

SELECT distinctrow
  FLUJO AS F1,
  NATURALEZA AS N1,
  `GRUPO ESTADÍSTICO` AS G1, 
  count(FLUJO)   AS cuenta 
FROM trafico.`2019`
group by FLUJO, NATURALEZA, `GRUPO ESTADÍSTICO`

So I know the records for each kind of traffic combination Now I want to set a threshold: 25% of the average of each type of traffic, which I get to know with the following query Q2

SELECT distinctrow
  FLUJO AS F1,
  NATURALEZA AS N1,
  `GRUPO ESTADÍSTICO` AS G1, 
  avg(TONELADAS)/4   AS FILTRO 
FROM trafico.`2019`
group by FLUJO, NATURALEZA, `GRUPO ESTADÍSTICO`

What I can’t do is this: Calculate the first query, but only records that meet the condition that the field “TONELADAS” is above the threshold of the second query: avg(TONELADAS)/4 of the records with these equal variables “FLUJO”, “NATURALEZA” abd ‘GRUPO ESTADÍSTICO’ And make a query of all the records in the table that meet the condition that their “TONELADAS” field is greater than the threshold of the second query (Q2) I tried to combine both Q1 and Q2 queries, but I didn’t succeed Any help would be more than welcome. Thank you very much in advance.

Answer

If you want to filter out rows from the first query you’ll need to add a WHERE clause, of course. The tricky part is that the value you want to filter against needs to be computed as the result of another query.

Now, the solution is to compute this other query as a table expression (f in this example) and then join it with the first query. Once you do that, writing the filtering condition is trivial since both values are now in the same scope.

For example:

SELECT
  t.FLUJO AS F1,
  t.NATURALEZA AS N1,
  t.`GRUPO ESTADÍSTICO` AS G1, 
  count(*) AS cuenta 
FROM trafico.`2019` t
join (
  SELECT
    FLUJO,
    NATURALEZA,
    `GRUPO ESTADÍSTICO`, 
    avg(TONELADAS) / 4 AS FILTRO 
  FROM trafico.`2019`
  group by FLUJO, NATURALEZA, `GRUPO ESTADÍSTICO`
) f ON f.FLUJO = t.FLUJO
   AND f.NATURALEZA = t.NATURALEZA
   AND f.`GRUPO ESTADÍSTICO` = t.`GRUPO ESTADÍSTICO`
WHERE t.TONELADAS > f.FILTRO
group by t.FLUJO, t.NATURALEZA, t.`GRUPO ESTADÍSTICO`