Home » SQL & PL/SQL » SQL & PL/SQL » Need to populate two more columns (Oracle 11g)
Need to populate two more columns [message #651756] |
Tue, 24 May 2016 10:11 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/2ee91e5969141486d5b7f7cff0a4e1e0?s=64&d=mm&r=g) |
arifs3738
Messages: 39 Registered: November 2015 Location: India
|
Member |
|
|
Input
ORDER_DATE ORDER_ID PROD_ID QTY PRICE
01-JUL-16 1 P1 5 5
01-JUL-16 2 P2 2 10
01-JUL-16 3 P3 10 25
01-JUL-16 4 P1 20 5
02-JUL-16 5 P3 5 25
02-JUL-16 6 P4 6 20
02-JUL-16 7 P1 2 5
02-JUL-16 8 P5 1 50
02-JUL-16 9 P6 2 50
02-JUL-16 10 P2 4 10
SQL Query
SELECT MAX(SOLD_AMT) AS AMT FROM
(SELECT T1.*, QUANTITY * PRICE AS SOLD_AMT FROM T1)
GROUP BY ORDER_DATE
Output:
AMT
250
125
Question:
I want output as below:
ORDER_DATE PRODUCT_ID AMT
01-JUL-11 P3 250
02-JUL-11 P3 125
|
|
|
|
|
Re: Need to populate two more columns [message #651760 is a reply to message #651759] |
Tue, 24 May 2016 11:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/2ee91e5969141486d5b7f7cff0a4e1e0?s=64&d=mm&r=g) |
arifs3738
Messages: 39 Registered: November 2015 Location: India
|
Member |
|
|
Please find Test Data:
SELECT TO_CHAR(TO_DATE('01-JUL-2016','DD-MON-YYYY'), 'DD-MON-YY') AS ORDER_DATE, 1 AS ORDER_ID, 'P1' AS PRODUCT_ID, 5 AS QUANTITY, 5 AS PRICE FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('01-JUL-2016','DD-MON-YYYY'), 'DD-MON-YY'), 2, 'P2', 2, 10 FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('01-JUL-2016','DD-MON-YYYY'), 'DD-MON-YY'), 3, 'P3', 10, 25 FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('01-JUL-2016','DD-MON-YYYY'), 'DD-MON-YY'), 4, 'P1', 20, 5 FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('02-JUL-2016','DD-MON-YYYY'), 'DD-MON-YY'), 5, 'P3', 5, 25 FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('02-JUL-2016','DD-MON-YYYY'), 'DD-MON-YY'), 6, 'P4', 6, 20 FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('02-JUL-2016','DD-MON-YYYY'), 'DD-MON-YY'), 7, 'P1', 2, 5 FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('02-JUL-2016','DD-MON-YYYY'), 'DD-MON-YY'), 8, 'P5', 1, 50 FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('02-JUL-2016','DD-MON-YYYY'), 'DD-MON-YY'), 9, 'P6', 2, 50 FROM DUAL
UNION ALL
SELECT TO_CHAR(TO_DATE('02-JUL-2016','DD-MON-YYYY'), 'DD-MON-YY'), 10, 'P2', 4, 10 FROM DUAL;
|
|
|
|
|
Re: Need to populate two more columns [message #651763 is a reply to message #651762] |
Tue, 24 May 2016 12:57 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> with t1 as (
2 SELECT TO_DATE('01-JUL-2016','DD-MON-YYYY') AS ORDER_DATE, 1 AS ORDER_ID,
3 'P1' AS PRODUCT_ID, 5 AS QUANTITY, 5 AS PRICE
4 FROM DUAL
5 UNION ALL
6 SELECT TO_DATE('01-JUL-2016','DD-MON-YYYY'), 2, 'P2', 2, 10 FROM DUAL
7 UNION ALL
8 SELECT TO_DATE('01-JUL-2016','DD-MON-YYYY'), 3, 'P3', 10, 25 FROM DUAL
9 UNION ALL
10 SELECT TO_DATE('01-JUL-2016','DD-MON-YYYY'), 4, 'P1', 20, 5 FROM DUAL
11 UNION ALL
12 SELECT TO_DATE('02-JUL-2016','DD-MON-YYYY'), 5, 'P3', 5, 25 FROM DUAL
13 UNION ALL
14 SELECT TO_DATE('02-JUL-2016','DD-MON-YYYY'), 6, 'P4', 6, 20 FROM DUAL
15 UNION ALL
16 SELECT TO_DATE('02-JUL-2016','DD-MON-YYYY'), 7, 'P1', 2, 5 FROM DUAL
17 UNION ALL
18 SELECT TO_DATE('02-JUL-2016','DD-MON-YYYY'), 8, 'P5', 1, 50 FROM DUAL
19 UNION ALL
20 SELECT TO_DATE('02-JUL-2016','DD-MON-YYYY'), 9, 'P6', 2, 50 FROM DUAL
21 UNION ALL
22 SELECT TO_DATE('02-JUL-2016','DD-MON-YYYY'), 10, 'P2', 4, 10 FROM DUAL
23 ),
24 data as (
25 select ORDER_DATE, ORDER_ID, PRODUCT_ID, QUANTITY*PRICE amount,
26 rank() over
27 (partition by trunc(ORDER_DATE) order by QUANTITY*PRICE desc) rk
28 from t1
29 )
30 select ORDER_DATE, ORDER_ID, PRODUCT_ID, amount
31 from data
32 where rk = 1
33 /
ORDER_DATE ORDER_ID PR AMOUNT
----------- ---------- -- ----------
01-JUL-2016 3 P3 250
02-JUL-2016 5 P3 125
|
|
|
|
Goto Forum:
Current Time: Sun Jun 30 17:20:22 CDT 2024
|