I can’t get average

Question

I’m unable to sum up the qtd_itens_purchase with Count cod_person. The query runs normal, frequencybuys the displayed result is the same as the amount of items that comes from "qtd_itens_purchase". In short, I would like to add 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  

Answer 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

Answer 2

In his query you are grouping by purchase amount, which turns out to be wrong, since from what I understand you want the purchase frequency per day. Adjusting 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

Answer 3

If you want the total sum, divided by count partial (grouped), you can use a subquery (see if performace looks 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