Registration Quantity per Data Mysql

Question

I have the following scenario.

I have a sales chart, there is a field data_vendas (datetime) and a field cliente_id

Another client table, with the fields id, nome, data_cadastro etc..

What I need to do is via SQL, for example, within the month of JUNE, the sales that had.

Only that the information I need is how many customers bought in the same of JUNE and who have registered at the maximum 1 month. And how many customers bought within the same June, who registered a month ago.

Answer 1

The query will return the total sales ( totalVendas ), total purchases from registered users in the previous month who purchased this month ( totalVendasCadastroAnterior ) and total purchases from registered users in the current month who purchased this month ( totalVendasCadastroAtual ).

SQL:

SELECT
    COUNT( * ) totalVendas,
    SUM(
        CASE WHEN
            ( MONTH( c.data_cadastro ) = 5 AND YEAR( c.data_cadastro ) = 2016 )
        THEN 1
        ELSE 0
        END
        ) totalVendasCadastroAnterior,
    SUM(
        CASE WHEN
            ( MONTH( c.data_cadastro ) = 6 AND YEAR( c.data_cadastro ) = 2016 )
        THEN 1
        ELSE 0
        END
        ) totalVendasCadastroAtual
FROM vendas v
JOIN clientes c
    ON c.id = v.cliente_id
WHERE MONTH( v.data_vendas ) = 6 AND YEAR( v.data_vendas ) = 2016

An example of the PHP query:

<?php
// Configurações
$config = [
    'DB_NAME' => 'db',
    'DB_USER' => 'user',
    'DB_PASS' => 'senha',
    'MES_DADOS' => 6,
    'ANO_DADOS' => 2016,
];

$prevMonth = $config['MES_DADOS'] > 1 ? $config['MES_DADOS'] - 1 : $config['MES_DADOS'] + 11;

try
{
    // Abre conexão
    $con = new PDO( "mysql:host=localhost;dbname={$config['DB_NAME']}", $config['DB_USER'], $config['DB_PASS'] );
    // Executa query
    $stmt = $con->prepare( 'SELECT
                                COUNT( * ) totalVendas,
                                SUM(
                                    CASE WHEN
                                        ( MONTH( c.data_cadastro ) = :prevMonth AND YEAR( c.data_cadastro ) = :year )
                                    THEN 1
                                    ELSE 0
                                    END
                                    ) totalVendasCadastroAnterior,
                                SUM(
                                    CASE WHEN
                                        ( MONTH( c.data_cadastro ) = :month AND YEAR( c.data_cadastro ) = :year )
                                    THEN 1
                                    ELSE 0
                                    END
                                    ) totalVendasCadastroAtual
                            FROM vendas v
                            JOIN clientes c
                                ON c.id = v.cliente_id
                            WHERE MONTH( v.data_vendas ) = :month AND YEAR( v.data_vendas ) = :year' );

    $stmt->bindValue( ':month', $config['MES_DADOS'] );
    $stmt->bindValue( ':year', $config['ANO_DADOS'] );
    $stmt->bindValue( ':prevMonth', $prevMonth );

    $result = $stmt->execute();

    if($result !== false)
    {
        $numClientes = $stmt->fetchAll( PDO::FETCH_ASSOC )[0];
        echo "Total de vendas no mes: {$numClientes['totalVendas']}.<br />";
        echo "{$numClientes['totalVendasCadastroAtual']} clientes se cadastraram no mes de {$config['MES_DADOS']} e compraram esse mes.<br />";
        echo "{$numClientes['totalVendasCadastroAnterior']} clientes se cadastraram no mes de {$prevMonth} e compraram esse mes.<br />";
    }
}
catch( PDOException $ex )
{
    die( $ex->getMessage() );
}