I can not average

Question

I can not average

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

0

I'm not able to make the sum between qtd_itens_bought and count_count. The query runs normal, at the frequency the result is the same as the amount of items that comes from the "qtd_itens_compra". In short, I would like to add up all the items and divide by the amount of people, taking the cod_person.

select top 10 cod_pessoa as CodPessoa,
            dat_compra as DataCompra,   
            vlr_compra as ValorCompra,
            cod_transacao as CodigoTransacao,       

            sum(qtd_itens_compra)/count(cod_pessoa)as FrequenciaCompra 

 from tab_transacao
 where dat_compra between '2017-08-01' and '2018-08-01'
 group by cod_pessoa,
          dat_compra,   
          vlr_compra,
          cod_transacao

sql sql-server

asked by anonymous 13.08.2018 / 16:49

3 answers

1

There is a specific function for SQL averages, AVG :

SELECT      cod_pessoa              AS CodPessoa
        ,   dat_compra              AS DataCompra
        ,   vlr_compra              AS ValorCompra
        ,   cod_transacao           AS CodigoTransacao
        ,   AVG(qtd_itens_compra)   AS FrequenciaCompra 
FROM        tab_transacao
WHERE       dat_compra BETWEEN '2017-08-01' AND '2018-08-01'
GROUP BY    cod_pessoa
        ,   dat_compra
        ,   vlr_compra
        ,   cod_transacao

13.08.2018 / 16:55

0

If you want the sum total, divided by count partial (grouped), you can use a subquery (see if the performance is good):

select top 10 cod_pessoa as CodPessoa,
            dat_compra as DataCompra,   
            vlr_compra as ValorCompra,
            cod_transacao as CodigoTransacao,       
            (select sum(qtd_itens_compra) from tab_transacao)/count(cod_pessoa)as FrequenciaCompra 
 from tab_transacao
 where dat_compra between '2017-08-01' and '2018-08-01'
 group by cod_pessoa,
          dat_compra,   
          vlr_compra,
          cod_transacao

13.08.2018 / 18:25

Error when running app directly on iOS device "npm install" from a .tgz without internet connection

score 0 · Accepted Answer

In your query you are grouping by value of the purchase, which ends up being wrong, since from what I understand you want the frequency of purchase per day. Setting your query would look like this:

SELECT cod_pessoa AS CodPessoa,
      dat_compra AS DataCompra,
      cod_transacao AS CodigoTransacao,
      SUM(qtd_itens_compra) / COUNT(cod_pessoa) AS FrequenciaCompra
  FROM tab_transacao
WHERE dat_compra BETWEEN '2017-08-01' AND '2018-08-01'
GROUP BY dat_compra,
          cod_transacao