query_cartoes_tipo_parcelamento.sql

SELECT
  tipo,
  ROUND(SUM(valor) / 100, 2) AS total
FROM (
       SELECT
         pfp.valor AS valor,
         '2 a 6 Vezes' AS tipo
       FROM sqldados.xalog2
         INNER JOIN webpdv.pedido_forma_pagamento pfp
           ON (pfp.storeno = xalog2.storeno AND pfp.pdvno = xalog2.pdvno AND pfp.xano = xalog2.xano)
         INNER JOIN sqldados.paym
           ON (paym.no = pfp.paymno)
         LEFT JOIN sqldados.card
           ON (card.no = paym.cardno)
       WHERE xalog2.date BETWEEN '20140101' AND '20140331'
             AND xalog2.qtty > 0
             AND pfp.xatype = 3
             AND card.no IN (4, 6)
             AND pfp.numero_parcelas BETWEEN 2 AND 6
       GROUP BY pfp.id_pedido_forma_pagamento

       UNION ALL

       SELECT
         pfp.valor AS valor,
         '7 a 12 Vezes' AS tipo
       FROM sqldados.xalog2
         INNER JOIN webpdv.pedido_forma_pagamento pfp
           ON (pfp.storeno = xalog2.storeno AND pfp.pdvno = xalog2.pdvno AND pfp.xano = xalog2.xano)
         INNER JOIN sqldados.paym
           ON (paym.no = pfp.paymno)
         LEFT JOIN sqldados.card
           ON (card.no = paym.cardno)
       WHERE xalog2.date BETWEEN '20140101' AND '20140331'
             AND xalog2.qtty > 0
             AND pfp.xatype = 3
             AND card.no IN (4, 6)
             AND pfp.numero_parcelas BETWEEN 7 AND 12
       GROUP BY pfp.id_pedido_forma_pagamento

       UNION ALL

       SELECT
         pfp.valor AS valor,
         'Debito' AS tipo
       FROM sqldados.xalog2
         INNER JOIN webpdv.pedido_forma_pagamento pfp
           ON (pfp.storeno = xalog2.storeno AND pfp.pdvno = xalog2.pdvno AND pfp.xano = xalog2.xano)
         INNER JOIN sqldados.paym
           ON (paym.no = pfp.paymno)
         LEFT JOIN sqldados.card
           ON (card.no = paym.cardno)
       WHERE xalog2.date BETWEEN '20140101' AND '20140331'
             AND xalog2.qtty > 0
             AND pfp.xatype = 3
             AND card.no IN (5, 7)
       GROUP BY pfp.id_pedido_forma_pagamento

       UNION ALL

       SELECT
         pfp.valor AS valor,
         'Rotativo' AS tipo
       FROM sqldados.xalog2
         INNER JOIN webpdv.pedido_forma_pagamento pfp
           ON (pfp.storeno = xalog2.storeno AND pfp.pdvno = xalog2.pdvno AND pfp.xano = xalog2.xano)
         INNER JOIN sqldados.paym
           ON (paym.no = pfp.paymno)
         LEFT JOIN sqldados.card
           ON (card.no = paym.cardno)
       WHERE xalog2.date BETWEEN '20140101' AND '20140331'
             AND xalog2.qtty > 0
             AND pfp.xatype = 3
             AND pfp.numero_parcelas = 1
             AND card.no IN (4, 6)
       GROUP BY pfp.id_pedido_forma_pagamento
     ) AS tbl
GROUP BY tipo

query_tiket_medio.sql

SELECT
  tipo,
  ROUND(SUM(valor) / 100, 2) AS total,
  COUNT(DISTINCT(pedido)) AS numero_pedidos,
  ROUND( (SUM(valor) / 100) / COUNT(DISTINCT(pedido)), 2) AS tiket
FROM (
       SELECT
         CONCAT(pfp.storeno, pfp.ordno, pfp.date) AS pedido,
         pfp.valor AS valor,
         'Credito' AS tipo
       FROM sqldados.xalog2
         INNER JOIN webpdv.pedido_forma_pagamento pfp
           ON (pfp.storeno = xalog2.storeno AND pfp.pdvno = xalog2.pdvno AND pfp.xano = xalog2.xano)
         INNER JOIN sqldados.paym
           ON (paym.no = pfp.paymno)
         LEFT JOIN sqldados.card
           ON (card.no = paym.cardno)
       WHERE xalog2.date BETWEEN '20140101' AND '20140331'
             AND xalog2.qtty > 0
             AND pfp.xatype = 3
             AND card.no IN (4, 6)
       GROUP BY pfp.id_pedido_forma_pagamento

       UNION ALL
         
       SELECT
         CONCAT(pfp.storeno, pfp.ordno, pfp.date) AS pedido,
         pfp.valor AS valor,
         'Debito' AS tipo
       FROM sqldados.xalog2
         INNER JOIN webpdv.pedido_forma_pagamento pfp
           ON (pfp.storeno = xalog2.storeno AND pfp.pdvno = xalog2.pdvno AND pfp.xano = xalog2.xano)
         INNER JOIN sqldados.paym
           ON (paym.no = pfp.paymno)
         LEFT JOIN sqldados.card
           ON (card.no = paym.cardno)
       WHERE xalog2.date BETWEEN '20140101' AND '20140331'
             AND xalog2.qtty > 0
             AND pfp.xatype = 3
             AND card.no IN (5, 7)
       GROUP BY pfp.id_pedido_forma_pagamento

     ) AS tbl
GROUP BY tipo