CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `view_compraporlabano` AS
    SELECT 
        CONCAT(`itensnfe`.`Cod_Emitente`,
                '|',
                DATE_FORMAT(`itensnfe`.`Dat_Movimento`, '%Y')) AS `idLabAno`,
        `itensnfe`.`Cod_Emitente` AS `Cod_Emitente`,
        `itensnfe`.`Razao_Social` AS `Razao_Social`,
        COUNT(DISTINCT `itensnfe`.`protocolo`) AS `qtdNotas`,
        SUM(`itensnfe`.`Qtd_Pedido`) AS `qtdUnidades`,
        SUM(`itensnfe`.`Vlr_TotItem`) AS `vlrTotalCompras`,
        DATE_FORMAT(`itensnfe`.`Dat_Movimento`, '%Y') AS `ano`
    FROM
        `itensnfe`
    WHERE
        (`itensnfe`.`Tip_NF` = 'c')
    GROUP BY CONCAT(`itensnfe`.`Cod_Emitente`,
            '|',
            DATE_FORMAT(`itensnfe`.`Dat_Movimento`, '%Y')) , `itensnfe`.`Cod_Emitente` , `itensnfe`.`Razao_Social` , DATE_FORMAT(`itensnfe`.`Dat_Movimento`, '%Y')
    ORDER BY COUNT(DISTINCT `itensnfe`.`protocolo`) DESC