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

Asked

Viewed 536 times

2

I’m getting the following result:

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

I’d need it to stay that way:

 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, only 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);
sql sql-server
  • 2

    What sales table structure? And how do you differentiate the form of payment?

  • The structure of the sales table: Codvenda INT, Datavenda DATE, Valorvenda DECIMAL, Formapagamento INT. (Formapagamento 1 = Boleto, 2 = Cartão)

  • Gabriel, this is relevant information that should be in the question. You can edit it and include =)

3 answers

4


Set an example, see if it fits:

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)

http://sqlfiddle.com/#! 18/0c28a/2

1

You could achieve 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);

But so you must perform sub-discharge. If possible, I suggest using the code below and mounting 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 be something like below:

 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

  • In the second form the result would be 3 columns and 2 rows (billet/card) for each month? Something like this: Mes | Forma | Qtd
 1 | Boleto | 5
 1 | Cartão | 8
 2 | Boleto | 4
 2 | Cartão | 12

  • edited the answer, see if it was clear ;)

1

You can do it like 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 column 'Other' in case the payment method is different from 1 and 2.

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

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.