当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Most customer that order most product together with productname and customername and amount they ordered

问题描述:

Here is the table with the records insides

Customers 91

Employees 10

OrderDetails 518

Orders 196

Products 77

Shippers 3

Suppliers 29

and here is the sql query i thought but i am not sure if i am getting right result I need help

Select customername, productname, Sum(price * quantity) as price, max (quantity) as Qantity_ordered

From customers

Inner join orders

On customers.customerId = orders.customerid

Inner Join Orderdetails

On orders.orderid = orderdetails.orderid

Inner Join Products

On orderdetails.productid = products.productid

;

网友答案:

If I understand your question well, you will need the following statement:

Select customername, 
    productname, 
    max(Temp.price) as price,
    Temp.quantity as Qantity_ordered
FROM
(Select customerid, productid, Sum(price * quantity) as price, max (quantity) as Qantity_ordered
    From customers
    Inner join orders
    On  customers.customerId = orders.customerid
    Inner Join Orderdetails
    On orders.orderid = orderdetails.orderid
    Inner Join Products
    On orderdetails.productid = products.productid
    Group By customerid, productid) Temp
Inner Join customers 
On customerId = Temp.customerId
Inner join orders
On  Temp.customerId = orders.customerid
Inner Join Orderdetails
On orders.orderid = orderdetails.orderid
Inner Join Products p
On  p.productid = Temp.productid
where quantity = Temp.Qantity_ordered
Group By customername, 
   productname, 
   Temp.quantity 
分享给朋友:
您可能感兴趣的文章:
随机阅读: