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
LVL 2
niceoneishereAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
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 AnalystCommented:
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ãoMSSQL Senior EngineerCommented:
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

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

niceoneishereAuthor 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
PortletPaulEE Topic AdvisorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
niceoneishereAuthor Commented:
Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.