Generate a table from another 5

Question

I need help to build a query that brings data from 5 other tables. I will try to be as clear as possible, in case information is lacking, please inform me.

Come on:

I have the Midias table: http://jotacomdigital.com.br/imgs/midias.png

and the Proposals table: http://jotacomdigital.com.br/imgs/propostas.png

The other 3 tables is quiet, because I just need to bring a field, not needing sums or any other function.

The result must be this:

http://jotacomdigital.com.br/imgs/resultado.png

My Current query is this:

    SELECT
       data_proposta,
       propostas.source_code,
       sum(midias.custo) as custo,
       COUNT(data_proposta) AS propostas,
       SUM(if(precog_FK <> 9, 1, 0)) AS validas
    FROM propostas
    LEFT JOIN midias
      ON `data_proposta` = `midias`.`data`
    GROUP BY data_proposta

I’m in trouble when I have to do the sums of the media table costs.

Answer 1

I changed the query, it was as follows:

SELECT
    m.data,
    date_format(m.data,'%d-%m-%Y') AS mdata,
    m.midia_origem,
    p.source_code,
    m.campanha,
    COUNT(m.campanha) AS propostas,
    SUM(m.custo) as custo
FROM 
    propostas p
LEFT JOIN
    midias m
    ON m.source_code = p.source_code
GROUP BY
    mdata,m.campanha
ORDER BY 
    mdata ASC, m.source_code ASC

Now I don’t understand the need for the following code:

SUM(if(check mark <> 9, 1, 0)) AS validates

If in the table "proposals" the code "souce_code" is unique, the SUM does not make sense, it will always be 1 or 0 because there is only one record.

Another important topic is to create indexes to optimize the query. In the table "media" create the index in the following order: source_code, date, campaign

And in the table "proposed" create the index on the column "source_code".