Registration amount per MySQL Data

Question

Registration amount per MySQL Data

Navigation

#1 by (0 votes)

0

I have the following scenario.

I have a sales table, there is a data_vendas (datetime) field and a 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 they had.

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

php mysql

asked by anonymous 19.06.2016 / 07:01

1 answer

"more than one device / emulator" when trying to use the mobile in Android Studio Error Script mv: can not move new_name to new_name / original_name Directory not empty

score 0 · Answer 1

The query will return total sales ( totalVendas ), total purchases of users registered in the previous month who bought that month ( totalVendasCadastroAnterior ), and total purchases of registered users in the current month they purchased that month (% with%).

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 query with PHP:

<?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() );
}