Doubt with Postgresql (Sum & Count)

Question

Personal beauty? I’m studying SQL with Postgres and I’ve been doing well but I have an exercise to do which has swayed my head and I can not solve, and wanted you to help me not only by showing me how it does but also by explaining a little about the problem so I could understand why of each thing!

I have the following problem that I can not add the number of sales nor the total value of sales of the month :(

**List January Average Ticket for each active store, returning:

• Business unit code

• Store Average Ticket Value

Tips:

• The "average ticket" is calculated based on the value of the total unregistered sales/rebates of

  a company divided by the number of sales.

• Use the following tables: unidadenegocio, sale**

So far what I’ve managed to do is this...

    select
    unidadenegocio.id,
    venda.id,
    venda.valortotal,
    venda.datahorafechamento

    from
    unidadenegocio
    left join venda on venda.unidadenegocioid = unidadenegocio.id

    where datahorafechamento >= '2020-10-01'
    and datahorafechamento <= '2020-10-31 23:59:59';

Answer 1

You must use the GROUP BY clause and the AVG aggregation function.

SELECT
    unidadenegocio.id,
    AVG(venda.valortotal)
FROM unidadenegocio left join venda on venda.unidadenegocioid = unidadenegocio.id
WHERE datahorafechamento >= '2020-10-01 00:00:00'
  AND datahorafechamento <= '2020-10-31 23:59:59'
GROUP BY unidadenegocio.id;

For Postgresql you can simplify your WHERE clause with:

WHERE date_trunc('month', datahorafechamento) = '2020-10-01 00:00:00'::timestamp;

In place of AVG could use SUM / COUNT.