0

I have two queries in MySQL that are basically the same, where the only difference between them is a min() or max(), I would like to know if it is feasible to join the two.

I believe that the combination of the two will give me again of significant disengagement and less rework.

My goal is to know the "valor_bruto_atual" from the first day and the last day.

First query:

SELECT 
    titulo_id as titulo_id,
    SUM(valor_bruto_atual) AS valor_max,
    data_imports.data_import AS created_at,
    week(data_imports.data_import) AS weeknumber
FROM
    ativos_extratos
        INNER JOIN
    titulos ON titulo_id = titulos.id
        INNER JOIN
    representantes ON representante_id = representantes.id
        INNER JOIN
    data_imports ON data_import_id = data_imports.id
WHERE
    user_id = 1
        AND ativos_extratos.data_import_id IN (SELECT 
            MAX(ID) as max_id
        FROM
            data_imports
        WHERE
            data_import BETWEEN '2018-11-01' AND '2018-12-10'
        GROUP BY week(data_import))
        AND data_imports.data_import BETWEEN '2018-11-01' AND '2018-12-10'
GROUP BY titulos.nome_titulo , weeknumber
ORDER BY data_import ASC , titulos.nome_titulo ASC

Second query:

SELECT 
    titulo_id as titulo_id,
    SUM(valor_bruto_atual) AS valor_min,
    data_imports.data_import AS created_at,
    week(data_imports.data_import) AS weeknumber
FROM
    ativos_extratos
        INNER JOIN
    titulos ON titulo_id = titulos.id
        INNER JOIN
    representantes ON representante_id = representantes.id
        INNER JOIN
    data_imports ON data_import_id = data_imports.id
WHERE
    user_id = 1
        AND ativos_extratos.data_import_id IN (SELECT 
            MIN(ID) as min_id
        FROM
            data_imports
        WHERE
            data_import BETWEEN '2018-11-01' AND '2018-12-10'
        GROUP BY week(data_import))
        AND data_imports.data_import BETWEEN '2018-11-01' AND '2018-12-10'
GROUP BY titulos.nome_titulo , weeknumber
ORDER BY data_import ASC , titulos.nome_titulo ASC

The only change was the fancy name of the column and the min() or max() function inside the where condition.

  • Add table aliases to ALL fields in any place of each query. – Akina Dec 12 '18 at 18:04
  • Schematically: 1) Move subquery from WHERE .. IN to FROM section. 2) Alter it for selection both MIN and MAX. 3) Edit WHERE. Edit SELECT adding CASE selection dependent by current record is in MIN or in MAX. – Akina Dec 12 '18 at 18:07
  • @Akina I've added aliases, however I do not understand how to make these other changes – Bulfaitelo Dec 12 '18 at 18:16
0

It will be something close to

SELECT 
    titulo_id as titulo_id,
-- calculate both SUMs, select values for each dependent by what value it matches to
    SUM(CASE WHEN ativos_extratos.data_import_id = borders.min_id THEN valor_bruto_atual END) AS valor_min,
    SUM(CASE WHEN ativos_extratos.data_import_id = borders.max_id THEN valor_bruto_atual END) AS valor_max,
    data_imports.data_import AS created_at,
    week(data_imports.data_import) AS weeknumber
FROM
    ativos_extratos
        INNER JOIN
    titulos ON titulo_id = titulos.id
        INNER JOIN
    representantes ON representante_id = representantes.id
        INNER JOIN
    data_imports ON data_import_id = data_imports.id
-- moved subquery and condition by it from WHERE 
    INNER JOIN ( SELECT 
                    MIN(ID) as min_id,
                    MAX(ID) as max_id
                 FROM
                    data_imports
                 WHERE
                    data_import BETWEEN '2018-11-01' AND '2018-12-10'
                 GROUP BY week(data_import)
               ) borders ON ativos_extratos.data_import_id IN (borders.min_id, borders.max_id)
WHERE
    user_id = 1
-- removed subquery and condition were there
        AND data_imports.data_import BETWEEN '2018-11-01' AND '2018-12-10'
GROUP BY titulos.nome_titulo , weeknumber

I am too lazy to debug - do it by yourself.

  • I am very grateful, it worked perfectly, now I will learn how this query works. – Bulfaitelo Dec 12 '18 at 18:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.