Pick up sales media for a given month

Question

Pick up sales media for a given month

Navigation

#1 by (2 votes)

0

I would like to take the average sales of the month by product description and not date by day, month and year, but I do not know how to get it.

SELECT AVG(vend_qtde) AS qtde_vendas from tb_vendas WHERE esto_descricao like '% Saco pedra %' and vend_data like '% 03/01/2018 %' GROUP BY esto_descricao;

Script from my database:

CREATE TABLE tb_vendas
(
    vend_cod serial NOT NULL,
    esto_descricao character(30) NOT NULL,
    vend_preco double precision,
    vend_qtde integer,
    vend_nome_cliente character(35),
    vend_cep character(20),
    vend_bairro character(25),
    vend_endereco character(38),
    vend_estado character(20),
    vend_uf character(13),
    vend_data character(20),
    vend_hora character(20),
    CONSTRAINT tb_vendas_pkey PRIMARY KEY (esto_descricao, vend_cod)
)

Registrations:

mysql sql postgresql

asked by anonymous 04.01.2018 / 22:56

1 answer

php mysqli last record How to use Jquery functions in array?

score 2 · Accepted Answer

Do not use the 'like' for dates, use between or greater than and less than. Ex.:

select avg(a.vend_qtde) from tb_vendas a where a.esto_descricao like '%Saco pedra%' and vend_data between '2018-01-01' and '2018-01-30'

or

select avg(a.vend_qtde) from tb_vendas a where a.esto_descricao like '%Saco pedra%' and vend_data >= '2018-01-01' and vend_data <= '2018-01-30'

I also advise you to leave the field "esto_descricao" and any other that is variable text as type VARCHAR and not CHARACTER. Varchar it uses only the necessary bytes, the charcater will fill in the 35 "spaces" even if you use only 1.