CODEWITHSUNDEEP
sqloracle-database
James Henrick
James Henrick

Reputation: 71

Subquery "Finding customer who has the most purchases"

I'm having trouble creating a query with a sub query to find the one customer in my DB who has made the most purchases. I need to list his/her full name, product name, price and quantity. Here is what I have so far 

select first_name ||' '|| last_name "FullName", pt.name p.price,  sum(ps.quantity) 
from customers c 
join purchases ps on c.customer_id = ps.customer_id 
join products p on p.product_id = ps.product_id
join product_types pt on p.product_type_id = pt.product_type_id;

I need to use these three tables

Customers Table

Customer_ID
First_Name
Last_Name
DOB
Phone

Purchases Table

Product_ID
Customer_ID
Quantity

Products Table

Product_ID
Product_Type_ID
Name
Description
Price

Product Types Table

Product_Type_ID
Name

I am confused as where I should place the sub query (in the select row, from, having or where clause), if the arithmetic function should be placed in the select outer query or sub query. I know there are Nested subqueries, Correlated subqueries, Multiple-column subqueries, Multiple-row subqueries, Single-row subqueries. By the way, I am trying to do this in Oracle. Here is an image with my result, except I removed sum from quantity column. Also, updated link.

(http://i1294.photobucket.com/albums/b618/uRsh3RRaYm0nD/Capture100_zps1f951b07.jpg)

I'm sorry, I forgot to include a fourth table, as you can see there are two name columns, in products table and product type table. The difference is that in products table "Name" is the specific name of the product, as shown in my link. The product type "Name" column is the more general name of the product, such as books, dvds, cds, etc. I need to include the product type "Name column in my query not product's name column. Therefore, the end result should look something like this 

 FullName  ProductTypeName  Price        Quantity    
 John Brown    Book         Sumof4books   4
 John Brown    DVD          Sumof2DVDs    2
 John Brown    Magazine     Sumof1Mag     1

Upvotes: 2

Views: 9063

Answers (4)

dev
dev

Reputation: 1

select first_name ||' '|| last_name "FullName",name,quantity from customers,purchases,products where products.product_id = purchases.product_id and purchases.customer_id = customers.customer_id order by quantity;

This is the query you want

Upvotes: 0

bro
bro

Reputation: 1

SELECT CLIENT.CLIENTNO, CLIENT.CNAME, SUM(PURCHASE.AMOUNT) AS AMOUNT
FROM CLIENT
INNER JOIN PURCHASE
ON CLIENT.CLIENTNO = PURCHASE.CLIENTNO
WHERE CLIENT.CLIENTNO IN (
    SELECT CLIENTNO
    FROM (
        SELECT PURCHASE.CLIENTNO, SUM(PURCHASE.AMOUNT) AS AMOUNT
        FROM PURCHASE
        GROUP BY PURCHASE.CLIENTNO
        ORDER BY AMOUNT DESC
    )
WHERE ROWNUM = 1)
GROUP BY CLIENT.CLIENTNO, CLIENT.CNAME;

Upvotes: 0

Laurence
Laurence

Reputation: 10976

Here's one way to do it. It uses an analytic function to order customers by the total quantity of purchases: row_number() over (order by sum(quantity) desc). If there's more than one person with the same quantity, this will pick out only one.

It then takes this customer id and joins the rest of the tables in the obvious way to get the break down by product type.

Select
  c.FullName,
  pt.name,
  Sum(p.price * ps.quantity) price,
  sum(ps.quantity) quantity
From (
  Select
    c.Customer_ID,
    c.first_name ||' '|| c.last_name FullName,
    row_number() over (order by Sum(Quantity) desc) r
  From
    Purchases ps
      Inner Join
    Customers c
      On ps.Customer_ID = c.Customer_ID
  Group By
    c.Customer_ID,
    c.first_name ||' '|| c.last_name
  ) c
    Inner Join
  Purchases ps
    On c.Customer_ID = ps.Customer_ID
    Inner Join
  Products p
    On ps.Product_ID = p.Product_ID
    Inner Join
  Product_Types pt
    On p.Product_Type_ID = pt.Product_Type_ID
Where
  c.r = 1
Group By
  c.FullName,
  pt.name

Example Fiddle

For the second problem (show the customer who has the highest quantity for each product type, together with what they've spent on that product type)

Select
  c.FullName,
  c.name,
  c.price,
  c.quantity
From (
  Select
    c.first_name ||' '|| c.last_name FullName,
    pt.name,
    sum(p.price * ps.quantity) price,
    sum(ps.quantity) quantity,
    row_number() over (partition by pt.name order by Sum(Quantity) desc) r
  From
    Purchases ps
      Inner Join
    Customers c
      On ps.Customer_ID = c.Customer_ID
      Inner Join
    Products p
      On ps.Product_ID = p.Product_ID
      Inner Join
    Product_Types pt
      On p.Product_Type_ID = pt.Product_Type_ID
  Group By
    c.first_name ||' '|| c.last_name,
    pt.name
  ) c
Where
  c.r = 1

Example Fiddle

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20794

Here is the general idea. You can adapt it for your database tables.

select fred, barney, maxwilma
from bedrock join
(select max(wilma) maxwilma
from bedrock
group by fred ) flinstone on wilma = maxwilma

Upvotes: 0

Related Questions