Sum of Total per Client

Question

I want to know the total of each product that each customer has bought and I don't know how to add the total per customer.

The tables are as follows:

CREATE TABLE CLIENTES -- Customers
(   
    CODIGO NUMBER(4,0)  PRIMARY KEY,    -- customer ID
    NOMBRE VARCHAR2(30) NOT NULL,       -- number
    APELLIDOS VARCHAR2(30) NOT NULL,    -- surnames
    EDAD NUMBER(2,0) NOT NULL           -- age
);

CREATE TABLE PEDIDOS -- Orders
(
    NUM NUMBER(5,0) PRIMARY KEY,        -- order ID
    FECHA DATE NOT NULL,                -- date
    GASTOS_ENVIO NUMBER(5,2),           -- shipping costs
    FECHA_PREVISTA DATE NOT NULL,       -- expected (shipping) date
    TOTAL NUMBER(10,2) ,                -- total
    CLIENTE NUMBER(4,0),                -- customer ID

    CONSTRAINT CLIENTES_FK FOREIGN KEY (CLIENTE) REFERENCES CLIENTES (CODIGO)
);

CREATE TABLE PRODUCTOS -- Products
(
    CODIGO  NUMBER(5,0) PRIMARY KEY,    -- product ID
    NOMBRE  VARCHAR2(30) NOT NULL,      -- amount
    PRECIO   NUMBER(7,2) NOT NULL       -- price
);

CREATE TABLE LINEAS -- Lines
(   
    NUM NUMBER(2,0),                    -- line ID
    NUM_PEDIDO NUMBER(5,0),             -- order ID
    PRODUCTO NUMBER(5,0) NOT NULL ,     -- product ID
    CANTIDAD NUMBER(8,0) NOT NULL ,     -- customer ID
    IMPORTE NUMBER(6,2),                -- amount

    CONSTRAINT DETALLE_PK PRIMARY KEY (NUM, NUM_PEDIDO) , 
    CONSTRAINT PEDIDO_FK FOREIGN KEY (NUM_PEDIDO)
      REFERENCES  PEDIDOS (NUM) , 
    CONSTRAINT PRODUCTO_FK FOREIGN KEY (PRODUCTO)
      REFERENCES  PRODUCTOS (CODIGO) 
);

The query I am currently trying is:

SELECT c.CODIGO, pr.NOMBRE, COUNT (l.CANTIDAD) AS TOTALPEDIDO
FROM CLIENTES c
INNER JOIN PEDIDOS p ON p.CLIENTE= c.CODIGO
INNER JOIN LINEAS l ON l.NUM_PEDIDO=p.NUM
INNER JOIN PRODUCTOS pr ON pr.CODIGO=l.PRODUCTO
GROUP BY c.CODIGO,  pr.NOMBRE
ORDER BY c.CODIGO;

And it gives me the sum of the last order.

Can you guide me please?

Gracias

^{This question has been translated from Spanish to English with the assistance of Google Translate}

Answer 1

SUM and GROUP

If I understood well what you want to know is the total UNITS of each product (not money) each customer has bought in all their orders COMBINED.

If that is so, in your SELECT clause you should SUM(l.CANTIDAD) rather than COUNT() it, as you want to know the number of products, not the number of lines.

The reason your results are only consistent with individual orders rather than all of them as a whole is because the orders table 'PEDIDOS' should be present in the GROUP BY clause as well:

SELECT c.CODIGO, pr.NOMBRE, SUM(l.CANTIDAD) AS TOTALPEDIDO
FROM CLIENTES c, PEDIDOS p, LINEAS l, PRODUCTOS pr
WHERE p.CLIENTE    =   c.CODIGO
  AND l.NUM_PEDIDO =   p.NUM
  AND pr.CODIGO    =   l.PRODUCTO
GROUP BY c.CODIGO, p.NUM, pr.NOMBRE
ORDER BY c.CODIGO;