SQL query to show customer and total products and costs

Question

SQL query to show customer and total products and costs

Navigation

#1 by (0 votes)
#2 by (0 votes)

-2

Indicate Sql query where you get the following columns total purchases, grouped by customer and totalized for the following Tables: Data:

Tabla CLIENTE                   
CAMPO        TIPO
---------------------------
DNI          Char(10)
Nombre       Varchar(30)
Apellido     Varchar(30)


Tabla  Pedidos
CAMPO        TIPO
---------------------------    
npedido      Numérico(4)
fecha        Datetime
cantidad     Numeric(8)
monto        Money
Cliente_dni  Char(10)

Directions: Use the commands sum, count, group by, order by, compute, join

I am using the following:

select nombre, sum(cantidad), sum(monto)
from cliente, pedidos
group by nombre

The expected result would be if pedro jimenes buys 10 items of cost 10 each:

CustomerName - amount - amount

pedro jimenes 10 100

The result in the amount and amount column is the same for all customers.

How do I separate the products by customer?

sql

asked by Samuel Pereira 19.06.2018 в 04:49

source

2 answers

Help with a Javascript function to add several dataset to a chartjs from the database? File shared using Google Drive API access is restricted

score 0 · Answer 1

You must use the join clause to relate the records of both tables by means of the dni field in the customer table and the field Customer_dni in the Orders table. The Group by clause is used to group data, in this case it is grouped by means of the DNI column and thus the query returns grouped and totalized the customer records. Example: Select COMCAT (c.Name, "", c.Lastname) as customer, COUNT (not required) as OrderNumber, SUM (p.quantity) as Quantity, SUM (as.mount) as Total Amount FROM CUSTOMER c INNER JOIN Orders p ON (c.DNI = p.Client_DI) GROUP BY c.DNI ORDER BY c.Name;

Greetings.

score 0 · Answer 2

I found the solution in the following way:

select cliente.dni, cliente.nombre, cliente.apellido, SUM(pedidos2.cantidad), SUM(pedidos2.monto)
       from cliente
       inner join pedidos2 on cliente.dni = pedidos2.cliente_dni
group by cliente.dni, cliente.nombre, cliente.apellido
order by dni

Thank you.