Home » SQL & PL/SQL » SQL & PL/SQL » status based use of SUM function
status based use of SUM function [message #631948] |
Thu, 22 January 2015 03:53 |
|
suji6281
Messages: 131 Registered: September 2014
|
Senior Member |
|
|
Hello All,
Please help us to write a query to get the sum(amount) to ZERO if all lines status 'X'. If 2nd line staus set to 'X' then we
should get the sum(amount) to total of other two active rows.
Create a table.
SQL> CREATE TABLE PS_VENDOR_COST_TBL (VENDOR_ID VARCHAR2(11) NOT NULL,
2 LINE_NBR INTEGER(5) NOT NULL, STATUS VARCHAR2(11) NOT NULL, AMOUNT DECIMAL(26, 3) NOT NULL );
Table created
Insert rows into table.
SQL> INSERT INTO PS_VENDOR_COST_TBL VALUES ('12345', 1, 'A', 100);
1 row created.
SQL> INSERT INTO PS_VENDOR_COST_TBL VALUES ('12345', 2, 'A', 200);
1 row created.
SQL> INSERT INTO PS_VENDOR_COST_TBL VALUES ('12345', 3, 'A', 300);
1 row created.
SQL> SELECT VENDOR_ID, SUM(AMOUNT) FROM PS_VENDOR_COST_TBL GROUP BY VENDOR_ID;
1 row selected.
Output of above select statement.
output is 12345, 600.
SQL> UPDATE PS_VENDOR_COST_TBL SET STATUS = 'X' WHERE LINE_NBR = 2;
1 row Updated.
Requirement is to get sum(amount) of status = 'A' rows only. If there is no rows with status = 'A' then we should get only
one row with sum(amount) as ZERO.
Thank You!
|
|
|
Re: status based use of SUM function [message #631950 is a reply to message #631948] |
Thu, 22 January 2015 04:07 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Welcome to the forum!
Please read and follow the OraFAQ Forum Guide and How to use [code] tags, to enable us to help you.
I can't use INTEGER(5)
SQL> CREATE TABLE PS_VENDOR_COST_TBL
2 (
3 VENDOR_ID VARCHAR2(11) NOT NULL,
4 LINE_NBR INTEGER(5) NOT NULL,
5 STATUS VARCHAR2(11) NOT NULL,
6 AMOUNT DECIMAL(26, 3) NOT NULL
7 );
LINE_NBR INTEGER(5) NOT NULL,
*
ERROR at line 4:
ORA-00907: missing right parenthesis
SQL> CREATE TABLE PS_VENDOR_COST_TBL
2 (
3 VENDOR_ID VARCHAR2(11) NOT NULL,
4 LINE_NBR INTEGER NOT NULL,
5 STATUS VARCHAR2(11) NOT NULL,
6 AMOUNT DECIMAL(26, 3) NOT NULL
7 );
Table created.
SQL>
[Updated on: Thu, 22 January 2015 04:11] Report message to a moderator
|
|
|
|
|
Re: status based use of SUM function [message #631954 is a reply to message #631953] |
Thu, 22 January 2015 04:28 |
|
Barbara Boehmer
Messages: 9077 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl12c> SELECT * FROM ps_vendor_cost_tbl
2 /
VENDOR_ID LINE_NBR STATUS AMOUNT
----------- ---------- ----------- ----------
12345 1 A 100
12345 2 A 200
12345 3 A 300
3 rows selected.
SCOTT@orcl12c> SELECT vendor_id, SUM(DECODE(status,'A',amount,0))
2 FROM ps_vendor_cost_tbl
3 GROUP BY vendor_id
4 /
VENDOR_ID SUM(DECODE(STATUS,'A',AMOUNT,0))
----------- --------------------------------
12345 600
1 row selected.
SCOTT@orcl12c> UPDATE ps_vendor_cost_tbl
2 SET status = 'X'
3 WHERE line_nbr = 2
4 /
1 row updated.
SCOTT@orcl12c> SELECT * FROM ps_vendor_cost_tbl
2 /
VENDOR_ID LINE_NBR STATUS AMOUNT
----------- ---------- ----------- ----------
12345 1 A 100
12345 2 X 200
12345 3 A 300
3 rows selected.
SCOTT@orcl12c> SELECT vendor_id, SUM(DECODE(status,'A',amount,0))
2 FROM ps_vendor_cost_tbl
3 GROUP BY vendor_id
4 /
VENDOR_ID SUM(DECODE(STATUS,'A',AMOUNT,0))
----------- --------------------------------
12345 400
1 row selected.
SCOTT@orcl12c> UPDATE ps_vendor_cost_tbl
2 SET status = 'X'
3 /
3 rows updated.
SCOTT@orcl12c> SELECT * FROM ps_vendor_cost_tbl
2 /
VENDOR_ID LINE_NBR STATUS AMOUNT
----------- ---------- ----------- ----------
12345 1 X 100
12345 2 X 200
12345 3 X 300
3 rows selected.
SCOTT@orcl12c> SELECT vendor_id, SUM(DECODE(status,'A',amount,0))
2 FROM ps_vendor_cost_tbl
3 GROUP BY vendor_id
4 /
VENDOR_ID SUM(DECODE(STATUS,'A',AMOUNT,0))
----------- --------------------------------
12345 0
1 row selected.
|
|
|
|
Goto Forum:
Current Time: Mon Mar 18 22:28:56 CDT 2024
|