Grouping and Summing in Oracle

Question

Grouping and Summing in Oracle

Navigation

#1 by (4 votes)

4

I have a Customer registry

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

And I have a purchase record 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 make a query that returns me the expense per group?

GRUPO         TOTAL_GASTO
FORNECEDORES  9.153
MANUTENCAO    13.360

What about each customer?

   CLIENTE  TOTAL_GASTO
   JOAO     5.130
   MARIA    13.360
   JOSE     4.023

sql oracle

asked by anonymous 05.05.2017 / 15:14

1 answer

How can I implement the GroupBy method in TypeScript? How to create a scrolling menu?

score 4 · Accepted Answer

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

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 to result into subsets that have matching values for one or more columns.

Free translation:

The GROUP BY clause groups the result into groups that match one or more columns.

SUM

SUM is an aggregate function that evaluates the sum of the expression over a set of rows.

Free translation:

SUM is an aggregate function that resolves the sum of the expression over the rowset.