Multiply columns and then sum rows: Oracle SQL-CodePudding

I'm trying to make a query in Oracle SQL Developer that must show the customer who has spend the biggest amount of money. So I have 4 tables: Cliente(Customer), Orden(Sale), Producto(Product) and a junction table to break many to many relationship between Orden and Producto.

CREATE TABLE Cliente(
    id_cliente INT NOT NULL PRIMARY KEY,
    Nombre VARCHAR(40) NOT NULL,
    Apellido VARCHAR(40) NOT NULL,
    Direccion VARCHAR(100) NOT NULL,
    Telefono INT NOT NULL,
    Tarjeta INT NOT NULL,
    Edad INT NOT NULL,
    Salario INT NOT NULL,
    Genero VARCHAR(5) NOT NULL,
    id_pais INT NOT NULL,
    CONSTRAINT fk_cliente_pais FOREIGN KEY(id_pais) REFERENCES Pais(id_pais)
    
);

CREATE TABLE Producto(
    id_producto INT NOT NULL PRIMARY KEY,
    Nombre VARCHAR(40) NOT NULL,
    Precio DECIMAL(10,2) NOT NULL,
    id_categoria INT NOT NULL,
    CONSTRAINT fk_categoria FOREIGN KEY(id_categoria) REFERENCES Categoria(id_categoria)
)

CREATE TABLE Orden(
    id_orden INT NOT NULL,
    linea_orden INT NOT NULL,
    fecha_orden DATE NOT NULL,
    id_cliente INT NOT NULL,
    id_vendedor INT NOT NULL,
    id_producto INT NOT NULL,
    cantidad INT NOT NULL,
    CONSTRAINT fk_orden_cliente FOREIGN KEY(id_cliente) REFERENCES Cliente(id_cliente),
    CONSTRAINT fk_orden_vendedor FOREIGN KEY(id_vendedor) REFERENCES Vendedor(id_vendedor),
    CONSTRAINT fk_orden_producto FOREIGN KEY(id_producto) REFERENCES Producto(id_producto),
    CONSTRAINT pk_orden PRIMARY KEY(id_orden, linea_orden)
);

DROP TABLE Detalle;
CREATE TABLE Detalle(
    id_detalle INT GENERATED ALWAYS AS IDENTITY,
    id_producto INT NOT NULL,
    id_orden INT NOT NULL,
    linea_orden INT NOT NULL,
    precio INT NOT NULL,
    cantidad INT NOT NULL,
    CONSTRAINT DETALLE_PRODUCTO
        FOREIGN KEY (id_producto)
        REFERENCES Producto (id_producto),
    CONSTRAINT DETALLE_ORDEN
        FOREIGN KEY (id_orden, linea_orden)
        REFERENCES Orden (id_orden, linea_orden),
    CONSTRAINT DETALLE_pk PRIMARY KEY(id_detalle, id_producto, id_orden)
);

On Table Detalle: precio (price), cantidad(quantity)

So I'm trying to get the Maximum total amount that a Customer has purchased by this query:

SELECT Cl.id_cliente, Cl.Nombre, Cl.Apellido,SUM( Detalle.precio * Detalle.cantidad) AS TOTAL
FROM Orden
INNER JOIN Cliente Cl ON Cl.id_cliente = Orden.id_cliente
INNER JOIN Detalle ON Detalle.id_orden = Orden.id_orden
GROUP BY Cl.id_cliente, Cl.Nombre, Cl.Apellido
ORDER BY TOTAL DESC;

But in the result, the TOTAL exceeds a lot from the right result, since I have another file with the result it should show.

CodePudding user response：

I rewritten your query:

Select Cl.id_cliente, Cl.Nombre, Cl.Apellido,TOTAL 
from(
      SELECT Orden.id_cliente, SUM( Detalle.precio * Detalle.cantidad) AS TOTAL
        FROM Detalle 
        JOIN Orden ON Detalle.id_orden = Orden.id_orden
       GROUP BY Orden.id_cliente

) Orden
JOIN Cliente Cl ON Cl.id_cliente = Orden.id_cliente
ORDER BY TOTAL DESC
;

CodePudding user response：

The data from details is summed multiple times, so calaculate them before joining

SELECT Cl.id_cliente, Cl.Nombre, Cl.Apellido,SUM(TOTAL ) AS TOTAL
FROM Orden
INNER JOIN Cliente Cl ON Cl.id_cliente = Orden.id_cliente
INNER JOIN (SELECT id_orden, SUM( Detalle.precio * Detalle.cantidad) AS TOTAL FROM Detalle GROUP BY id_orden) Detalle ON Detalle.id_orden = Orden.id_orden
GROUP BY Cl.id_cliente, Cl.Nombre, Cl.Apellido
ORDER BY TOTAL DESC;