Dovberman
asked on
Oracle SQL to sum over rows
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.
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.
ASKER
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.
ASKER
I tried this.
Customer is work item (w) T_PSA_MASTER, Orders is letters (l) T_PSA_LETTERS
*/
SELECT T_PSA_MASTER.psa_master_se qnum, w.PLANNING_STATUS, LISTAGG(L.PSA_LETTERS_SEQN UM, ',')
"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.PLAN NING_STATU S
ORDER BY w.psa_master_seqnum;
/
Error
ORA-02000 missing WITHIN keyword
"LetterIDs" is highlighted in red.
What am I missing?
Thanks
Customer is work item (w) T_PSA_MASTER, Orders is letters (l) T_PSA_LETTERS
*/
SELECT T_PSA_MASTER.psa_master_se
"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.PLAN
ORDER BY w.psa_master_seqnum;
/
Error
ORA-02000 missing WITHIN keyword
"LetterIDs" is highlighted in red.
What am I missing?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent.
This sql query does this for you
Open in new window
More on the LISTAGG function
http://docs.oracle.com/cd/E11882_01/server.112/e10592/functions089.htm
Regards,
Tomas Helgi