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 Go to next message
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 Go to previous messageGo to next message
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 #631952 is a reply to message #631950] Thu, 22 January 2015 04:17 Go to previous messageGo to next message
suji6281
Messages: 131
Registered: September 2014
Senior Member
Thank You for the correction in create table script.
Re: status based use of SUM function [message #631953 is a reply to message #631948] Thu, 22 January 2015 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just use the standard SUM with a DECODE or CASE in it and using NVL to zero the NULL value if there are no active rows.

(Note: your requirement is not the more efficient one, your calling application should set the variable to 0 if no rows are returned by a query which only select the relevant rows, that is those with status active.
Efficiency is not just at SQL level, it starts at requirements one.)

Re: status based use of SUM function [message #631954 is a reply to message #631953] Thu, 22 January 2015 04:28 Go to previous messageGo to next message
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.

Re: status based use of SUM function [message #631956 is a reply to message #631954] Thu, 22 January 2015 05:00 Go to previous message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Tell me and I'll forget; show me and I may remember; involve me and I'll understand

Previous Topic: Confusion about data length in CLOB type variable in PL/SQL
Next Topic: Populate List from Java
Goto Forum:
  


Current Time: Mon Mar 18 22:28:56 CDT 2024