gistfile1.sql

SELECT
  SUM(valor_liquido) AS valor_liquido,
  SUM(cmv) AS cmv,
  tipo,
  tipo_nome,
  empno,
  emp.name AS vendedor,
  SUM(trocas_devolucoes) AS devolvida
FROM (
       SELECT
         ROUND(SUM(qtty / 1000 * ABS(price)), 0) AS valor_liquido,
         ROUND(SUM(IF(qtty > 0, (qtty / 1000 * ABS(cost_fifo / 100)), 0)), 0) AS cmv,
         IF(qtty < 0, 1, 0) AS devolucao_troca,
         ROUND(SUM(IF(qtty > 0,
                      (qtty / 1000 * ABS(price))
         , 0)), 0) AS valor_vendas,
         ROUND(SUM(IF(qtty < 0,
                      (qtty / 1000 * price)
         , 0)), 0) AS trocas_devolucoes,
         x2.storeno,
         x2.date,
         prd.typeno AS 'tipo',
         type.name AS 'tipo_nome',
         eord.empno
       FROM sqldados.xalog2 AS x2
         LEFT JOIN sqldados.prd
           ON (prd.no = x2.prdno)
         LEFT JOIN sqldados.type
           ON (type.no = prd.typeno)
         LEFT JOIN sqlpdv.pxa
           ON (pxa.storeno = x2.storeno AND pxa.pdvno = x2.pdvno AND pxa.xano = x2.xano)
         LEFT JOIN sqldados.eord
           ON (eord.storeno = pxa.storeno AND eord.ordno = pxa.eordno)
       WHERE 1 = 1
             AND x2.date BETWEEN '20140401' AND '20140431'
             AND x2.storeno = 2
       GROUP BY prd.typeno
     ) tabela1
  LEFT JOIN sqldados.emp on (emp.no = tabela1.empno)
GROUP BY tipo, empno
ORDER BY valor_liquido DESC