We help IT Professionals succeed at work.

display all orders for a particuler customer from orders table in sql

So I have a sql table called orders in which I am importing order information thru excel sheet. Each row of the excel sheet contains item details from a particular order that belong a customer.

The issue I am facing is a customer can contain more than one order. The only unique fields I see in the file is order_id and billing_email address.

How do I display all the orders a particular customer has placed. I am attaching a sample excel file, as you can see I am trying to display all the orders placed for example by john doe with the total order amt he ever purchased.

Any advice is appreciated.

Thanks
smaple.xlsx
Comment
Watch Question

Top Expert 2010

Commented:
A better design would have been to include a customer ID on that table.  Also, please explain how the detail rows for order #1234 have a total order of 10.  Based on the line items, I was rather expecting it to be 13.

Also, it's not entirely clear what you want for the output.  Based on the sample provided above, please mock up what the result should be.  For example: one line per unique customer email, with a sum of ORDER_TOTL?  One row per customer email and order ID?  Something else entirely?
Pratik MakwanaData Analyst

Commented:
Try this////////////////////////////
select ORDER_ID,ORDER_DATE,BILL_FNAME,BILL_LNAME,BILL_EMAIL,PROD_CODE,PROD_NAME,PROD_PRICE,PROD_QUANT,ORDER_TOTL
from (
  select ORDER_ID,ORDER_DATE,BILL_FNAME,BILL_LNAME,BILL_EMAIL,
  STUFF ((Select ' '+PROD_CODE From orderdetail p1 Where p2.ORDER_ID=P1.ORDER_ID and p2.ORDER_DATE=p1.ORDER_DATE
            For XML PATH('')),1,1,'') as PROD_CODE,
  STUFF ((Select ' '+PROD_NAME From orderdetail p1 Where p2.ORDER_ID=P1.ORDER_ID and p2.ORDER_DATE=p1.ORDER_DATE
            For XML PATH('')),1,1,'') as PROD_NAME
  , row_number() over (partition by ORDER_ID order by ORDER_ID) r
  , sum(PROD_PRICE) over (partition by ORDER_ID) as PROD_PRICE
  , sum(PROD_QUANT) over (partition by ORDER_ID) as PROD_QUANT
  , sum(ORDER_TOTL) over (partition by ORDER_ID) as ORDER_TOTL
  from orderdetail p2
) firstRow
where firstRow.r = 1
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
If I understood what you want can be solved with a DISTINCT clause:
SELECT DISTINCT BILL_FNAME, BILL_LNAME, BILL_EMAIL, ORDER_ID, ORDER_DATE, ORDER_TOTL
FROM Smaple

Open in new window

Author

Commented:
Thanks guys for all the replies appreciate it.

Yes the order total for #1234 should be 13 It was my mistake while creating the sample file, here is what I am looking for basically to get all the orders placed by John Doe along with the product info and total sales amt between any given date range.

Thanks
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
When you don't actually provide the "expected result" we end-up guessing what your words indicate.details divided by order
Is this the expected result?expected?
for that result (above) use this query:
SELECT
      ORDER_ID
    , ORDER_DATE
    , BILL_FNAME
    , BILL_LNAME
    , BILL_EMAIL
    , AVG( PROD_PRICE ) AS AV_ORDER_PRICE
    , SUM( PROD_QUANT ) AS ORDER_QUANT
    , SUM( ORDER_TOTL ) AS ORDER_TOTL
FROM orderdetails
GROUP BY ORDER_ID
       , ORDER_DATE
       , BILL_FNAME
       , BILL_LNAME
       , BILL_EMAIL
;

Open in new window

Author

Commented:
Thanks