Group By - Aggregation error?

Asked

Viewed 21 times

1

good afternoon! Thank you for your time. I’m starting in SQL (and programming as a whole!).

At my base, I have several lines for the NRPROT with different prices, low values and different resource. What I need is to group the NRPROT and its respective values. I do not know if it was clear... but I assembled the query below and Access does not allow. It says that values are missing for aggregation. I should put all the columns that are in select?

SELECT 
  NRPROT,
  CODCONTR, 
  MARCA, 
  NRPROT, 
  DTPGTO, 
  VALORRECURSO, 
  VALORBAIXA, 
  VLRGLO, 
  VLRGLO * 0.47 AS NEGOCIACAO_RECURSO, 
  VLRGLO * 0.53 AS NEGOCIACAO_BAIXA 
FROM TESTE_1
GROUP BY NRPROT;
sql ms-access

  • the "group by" asks you to put all fields that are before From, in the order you want to group

  • 1

    Thank you! I did not know this "detail". rs

1 answer

1


When you use the Group By, all columns that will be unique in select should be used for grouping.

For example, if you want to know how many students are the same age and live in the same country, your query would be something like

SELECT idade, pais, count(*) as total
FROM alunos
GROUP BY idade, pais

So your query just needs to add more clauses to the group by:

SELECT 
  NRPROT, CODCONTR, MARCA, NRPROT, DTPGTO, VALORRECURSO, VALORBAIXA, VLRGLO, 
  VLRGLO * 0.47 AS NEGOCIACAO_RECURSO, VLRGLO * 0.53 AS NEGOCIACAO_BAIXA 
FROM TESTE_1
GROUP BY NRPROT, CODCONTR, MARCA, NRPROT, DTPGTO, VALORRECURSO, VALORBAIXA, VLRGLO

Browser other questions tagged sql ms-access

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