How to show quantity of sales per form of payment per month in SQL Server

2

I'm having the following result:

Mes | QtdPorMes
 1  |    124
 2  |    102
 3  |    84
 4  |    32

I would need it to look like this:

 Mes |   Boleto  | Cartão  | Total
  1  |     100   |   24    |  124
  2  |     82    |   20    |  82   
  3  |     34    |   50    |  84   
  4  |     12    |   20    |  32  

What clauses should I use?

The table is fictitious, just for studies.

SELECT
    MONTH(DataVenda) AS 'Mes',
    COUNT(FormaPagamento) AS 'QtdPorMes'
FROM
    vendas
WHERE
    DataVenda > '2018-01-01'
GROUP BY MONTH(DataVenda)
ORDER BY MONTH(DataVenda);
    
asked by anonymous 10.04.2018 / 15:47

3 answers

4

I've set an example, see if it suits you:

create table tempVendas
(
    DataVenda datetime,
    FormaPagamento varchar(100)
)

insert into  tempVendas values ('2018-01-01', 'Boleto')
insert into  tempVendas values ('2018-01-01', 'Boleto')
insert into  tempVendas values ('2018-01-01', 'Boleto')
insert into  tempVendas values ('2018-01-01', 'Cartão')
insert into  tempVendas values ('2018-01-01', 'Cartão')

insert into  tempVendas values ('2018-02-01', 'Boleto')
insert into  tempVendas values ('2018-02-01', 'Cartão')
insert into  tempVendas values ('2018-02-01', 'Cartão')
insert into  tempVendas values ('2018-02-01', 'Cartão')

insert into  tempVendas values ('2018-03-01', 'Boleto')
insert into  tempVendas values ('2018-03-01', 'Boleto')
insert into  tempVendas values ('2018-03-01', 'Boleto')
insert into  tempVendas values ('2018-03-01', 'Cartão')
insert into  tempVendas values ('2018-03-01', 'Cartão')
insert into  tempVendas values ('2018-03-01', 'Cartão')
insert into  tempVendas values ('2018-03-01', 'Cartão')
insert into  tempVendas values ('2018-03-01', 'Cartão')


SELECT month(dataVenda) [Mes],
count(case when FormaPagamento = 'Boleto' then FormaPagamento end) 'Boleto',
count(case when FormaPagamento = 'Cartão' then FormaPagamento end) 'Cartão',
count(FormaPagamento) Total
FROM tempVendas
group by month(dataVenda)
order by month(dataVenda)

link

    
10.04.2018 / 16:05
1

You would get the expected result with the following query:

SELECT
    MONTH(DataVenda) AS 'Mes',
    (SELECT COUNT(*) FROM vendas V2 WHERE V2.DataVenda > '2018-01-01' AND MONTH(V2.DataVenda) = V1.DataVenda AND FormaPagamento = 1) as 'Boleto',
    (SELECT COUNT(*) FROM vendas V2 WHERE V2.DataVenda > '2018-01-01' AND MONTH(V2.DataVenda) = V1.DataVenda AND FormaPagamento = 2) as 'Cartão', 
    COUNT(FormaPagamento) AS 'QtdPorMes'
FROM
    vendas V1
WHERE
    DataVenda > '2018-01-01'
GROUP BY MONTH(DataVenda)
ORDER BY MONTH(DataVenda);

However you need to execute subqueries . If possible, I suggest using the code below and mount the QtdPorMes total via code:

SELECT
    MONTH(DataVenda) AS 'Mes',
    FormaPagamento, 
    COUNT(FormaPagamento) AS 'QtdPorMesForma'
FROM
    vendas V1
WHERE
    DataVenda > '2018-01-01'
GROUP BY MONTH(DataVenda), FormaPagamento
ORDER BY MONTH(DataVenda);

So, the return would look something like this:

 Mes | FormaPagamento | QtdPorMesForma
  1  |         1      |       100
  1  |         2      |       24    
  2  |         1      |       62
  2  |         2      |       20
  3  |         1      |       34
  3  |         2      |       50    
  4  |         1      |       12
  4  |         2      |       20    
    
10.04.2018 / 15:57
1

You can do this:

SELECT
    MONTH(DataVenda) AS 'Mes',
    Sum( CASE WHEN FormaPagamento = 1 THEN 1 ELSE 0 END) AS 'Boleto',
    Sum( CASE WHEN FormaPagamento = 2 THEN 1 ELSE 0 END) AS 'Cartão',       
    Sum( CASE WHEN FormaPagamento <> 1 AND FormaPagamento <> 2 THEN 1 ELSE 0 END) AS 'Outros',      
    COUNT(*) AS 'QtdTotal'
FROM
    vendas
WHERE
    DataVenda > '2018-01-01'
GROUP BY MONTH(DataVenda)
ORDER BY MONTH(DataVenda);

Note that I have already included an additional 'Other' column in case the payment method is different from 1 and 2.

The problem is that with each new payment method that you need to include as a column, this select will need to be changed.

    
10.04.2018 / 16:09