Cluster and Sum in Oracle

Question

I have a customer record

ID    NOME  GRUPO
2033  JOAO  FORNECEDORES
2044  MARIA MANUTENCAO
2055  JOSE  FORNECEDORES

And I have a record of purchases made by each customer

ID_CLIENTE  VALOR_COMPRA
2033        4.000
2033        1.130
2044        8.930
2044        4.430
2055        4.023

How to perform a query that returns my expenses per group?

GRUPO         TOTAL_GASTO
FORNECEDORES  9.153
MANUTENCAO    13.360

And for every customer?

   CLIENTE  TOTAL_GASTO
   JOAO     5.130
   MARIA    13.360
   JOSE     4.023

Answer 1

You can use the clause GROUP_BY together with the function SUM:

SELECT cli.grupo,
       SUM(com.valor_compra) AS total_gasto
  FROM clientes cli
       INNER JOIN compras com ON com.id_cliente = cli.id
 GROUP BY grupo;

In the case of the client:

SELECT nome AS cliente,
       SUM(com.valor_compra) AS total_gasto
  FROM clientes cli
       INNER JOIN compras com ON com.id_cliente = cli.id
 GROUP BY grupo

GROUP BY

A GROUP BY clause groups a result into subsets that have matching values for one or more Columns.

In free translation:

The GROUP BY clause groups the result into groups corresponding to one or more columns.

---

SUM

SUM is an Aggregate Function that evaluates the sum of the Expression over a set of Rows.

In free translation:

SUM is an aggregate function that solves the sum of the expression over the set of lines.