SQL query for customer’s data

Tags:
SQL Query
SQL Server
The owner of the shop would like to see each customer's
  • id (name the column cus_id),
  • name (name the column cus_name)
  • id of their latest purchase (name the column latest_purchase_id)
  • the total quantity of all flowers purchased by the customer, in all purchases, not just the last purchase (name the column all_items_purchased).
Tables:- Table: customer
ID NAME COUNTRY
1 John Mill USA
2 Alex Blacksmith Australia
3 Patrick Pearson Australia
Table: purchase
ID CUSTOMER_ID YEAR
1 1 2011
2 1 2013
3 2 2012
4 2 2012
5 3 2014
6 3 2014
7 3 2015
Table: purchase_item
ID PURCHASE_ID NAME QUANTITY
1 1 Rose 10
2 1 Chrysanthemum 13
3 2 Tulip 7
4 2 Lily 8
5 2 Narciss 15
6 5 Chrysanthemum 20
7 5 Rose 21
8 6 Daffodil 3
9 6 Iris 45
10 7 Sunflower 11
11 7 Violet 28
12 7 Rose 30
13 3 Tulip 2
14 4 Rose 8
15 4 Lily 1


Software/Hardware used:
Oracle 10g
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

You just need to join the 3 tables and use the MAX and SUM aggregate functions.

This example assumes that the purchase id’s are generated sequentially because there is no other field to identify which one is the “latest” (something like a transaction date or similar).

SELECT
    c.id AS cus_id,
    c.name AS cus_name,
    MAX(p.id) AS latest_purchase_id,
    SUM(pi.quantity) AS all_items_purchased
FROM
    customer c
    JOIN purchase p
    ON c.id = p.customer_id
    JOIN purchase_item pi
    ON p.id = pi.purchase_id
GROUP BY c.id, c.name;

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    Sounds like a homework problem.

    What code have you tried? What problems or errors are you getting?
    133,790 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: