Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Oracle SQL to sum over rows

Posted on 2014-08-15
5
Medium Priority
?
606 Views
Last Modified: 2014-08-15
I need an output that displays a row for each customer and includes a column for each order placed by customer even those who placed no orders.

Additionally, the Order number column needs to display a comma delimited list of order numbers for customers who placed more than one order.

I have a Customer table and an Orders table
Customer cols: CustName,  CustID
Orders cols: OrderDate,  OrderID, CustID(foreign key)
--Select all customer rows even if there are customers who have no orders
SELECT Customer.CustID, Customer.CustName,
Orders.OrderID,Orders. OrderDate
FROM Customer
LEFT OUTER JOIN Customer  
ON CUSTOMER.CustID= Orders.CustID
WHERE CUSTOMER.CustID>= 11 AND CUSTOMER.CustID<=16
ORDER BY CUSTOMER.CustID;
CustID      CustName      OrderID            OrderDate
11      Mike            27            10/17/2013
11      Mike            32            10/20/2013
11      Charles                        
11      David            37            10/17/2013
11      James            27            10/26/2013

I need to combine the orders for Mike in a single row and display the order ID’s in a single cell
Where there is more than one order per customer.
CustID      CustName      OrderID            OrderDate
11      Mike            27,32            10/20/2013   (most recent order date for that customer.)
11      Charles                        
11      David            37            10/17/2013
11      James            27            10/26/2013

What are the recommended steps ?

Create a recordset, then step through then recordset.

Build a single sql statement that sums the rows when there are multiple orders per customer.

Thank you.
0
Comment
Question by:Dovberman
  • 3
  • 2
5 Comments
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 40263361
Hi!

This sql query does this for you

SELECT Customer.CustID, Customer.CustName, LISTAGG(Orders.OrderID, ',') OrdersIDs , LISTAGG(Orders. OrderDate, ',') WITHIN GROUP ( ORDER BY Orders.OrderDate DESC) OrderDates
FROM Customer
LEFT OUTER JOIN Customer  
ON CUSTOMER.CustID= Orders.CustID
WHERE CUSTOMER.CustID>= 11 AND CUSTOMER.CustID<=16
GROUP BY CUSTOMER.CustID,CUSTOMER.CustName 
ORDER BY CUSTOMER.CustID;

Open in new window


More on the LISTAGG function
http://docs.oracle.com/cd/E11882_01/server.112/e10592/functions089.htm


Regards,
     Tomas Helgi
0
 

Author Comment

by:Dovberman
ID: 40263391
I will try and give you feedback.  I need to rebuild the statement based on our own tables.  I used the generic customer order scenario for simplification.
0
 

Author Comment

by:Dovberman
ID: 40263531
I tried this.

Customer is work item (w) T_PSA_MASTER, Orders is letters (l) T_PSA_LETTERS

*/

SELECT T_PSA_MASTER.psa_master_seqnum, w.PLANNING_STATUS, LISTAGG(L.PSA_LETTERS_SEQNUM, ',')
"LetterIDs" , l.OrderDate
FROM T_PSA_MASTER w
LEFT OUTER JOIN T_PSA_LETTERS l  
ON w.psa_master_seqnum= l.psa_master_seqnum
WHERE w.psa_master_seqnum >= 11 AND w.psa_master_seqnum <=16
GROUP BY w.psa_master_seqnum,w.PLANNING_STATUS  
ORDER BY w.psa_master_seqnum;
/

Error
ORA-02000 missing WITHIN keyword
"LetterIDs" is highlighted in red.

What am I missing?

Thanks
0
 
LVL 26

Accepted Solution

by:
Tomas Helgi Johannsson earned 2000 total points
ID: 40263628
Hi!

You will have to do it like this

SELECT T_PSA_MASTER.psa_master_seqnum, w.PLANNING_STATUS, LISTAGG(L.PSA_LETTERS_SEQNUM, ',')
WITHIN GROUP (ORDER BY L.PSA_LETTERS_SEQNUM ) "LetterIDs" , l.OrderDate
FROM T_PSA_MASTER w
LEFT OUTER JOIN T_PSA_LETTERS l  
ON w.psa_master_seqnum= l.psa_master_seqnum
WHERE w.psa_master_seqnum >= 11 AND w.psa_master_seqnum <=16
GROUP BY w.psa_master_seqnum,w.PLANNING_STATUS  

Open in new window


Regards,
    Tomas Helgi
0
 

Author Closing Comment

by:Dovberman
ID: 40263872
Excellent.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question