Home » SQL & PL/SQL » SQL & PL/SQL » Subtract Positive values to negative values (Reports,6i,XP)
Subtract Positive values to negative values [message #403175] Thu, 14 May 2009 01:21 Go to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Dear Expert

Please help me to solve my query,I have 2 type of values in my EXC_ST_AMT column one type of data is in positive figure and other type of data is in negative figure.details are

CREATE TABLE B
(
 PROD_CD    VARCHAR2(3),
CHASSIS_NO VARCHAR2(30),
INV_DT DATE,
EXC_ST_AMT NUMBER(12,2));

INSERT INTO B
(PROD_CD,CHASSIS_NO,EXC_ST_AMT)
VALUES
(
'10','PHFGNSMXR7D142026',-581897);

INSERT INTO B
(PROD_CD,CHASSIS_NO,EXC_ST_AMT)
VALUES
(
'10','PHFGNSMXR7D142046',581897);

INSERT INTO B
(PROD_CD,CHASSIS_NO,EXC_ST_AMT)
VALUES
(
'10','PHFGNSMXR7D142302',-581897);


INSERT INTO B
PROD_CD,CHASSIS_NO,EXC_ST_AMT)
VALUES
(
('10','PHFGNSMXR7D142053',581897);

INSERT INTO B
(PROD_CD,CHASSIS_NO,EXC_ST_AMT)
VALUES
(
'10','PHFGNSMXR7D143015',581897);

INSERT INTO B
(PROD_CD,CHASSIS_NO,EXC_ST_AMT)
VALUES
(
'10','PHFGNSMXR7D142109',581897);

Insert into b
(
prod_cd,CHASSIS_NO,EXC_ST_AMT)
values
('12','PHFGNSMXR7D142061',-581897);
insert into b
(
prod_cd,CHASSIS_NO,EXC_ST_AMT)
values
('12','PHFGNSMXR7D142062',-581897);
insert into b
(
prod_cd,CHASSIS_NO,EXC_ST_AMT)
values
('12','PHFGNSMXR7D142063',581897);
insert into b
(
prod_cd,CHASSIS_NO,EXC_ST_AMT)
values
('12','PHFGNSMXR7D142064',581897);
insert into b
(
prod_cd,CHASSIS_NO,EXC_ST_AMT)
values
('12','PHFGNSMXR7D142065',581897);


Table contains the following data.
SQL> select * from b;

PRO CHASSIS_NO                     INV_DT    EXC_ST_AMT
--- ------------------------------ --------- ----------
10  PHFGNSMXR7D142026                           -581897
10  PHFGNSMXR7D142046                            581897
10  PHFGNSMXR7D142302                           -581897
10  PHFGNSMXR7D142053                            581897
10  PHFGNSMXR7D143015                            581897
10  PHFGNSMXR7D142109                            581897
12  PHFGNSMXR7D142061                           -581897
12  PHFGNSMXR7D142062                           -581897
12  PHFGNSMXR7D142063                            581897
12  PHFGNSMXR7D142064                            581897
12  PHFGNSMXR7D142065                            581897



Now i want to count chassis_no column (product wise ) but  query is working into the following manner.

first query count chassis_no (productwise) 
second count total no of positive values productwise 
third count total no of negative values productwise
fourth subtract totalcount of positive values into negative value
Display result like this 



prod_cd count(chassis_no)

10           4              
12           1

in case of prod_cd 10 ,total postive =5 and total negative values=2 and the answer is 4
in case of prod_cd 12 ,total postive =3 and total negative values=2 and the answer is 1



Kindly advice
Re: Subtract Positive values to negative values [message #403177 is a reply to message #403175] Thu, 14 May 2009 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand why 5 and 2 gives 4.

You can use SIGN to know if a value is positive, negative or zero (by the way, you don't talk about 0).
Then you can COUNT the number of SIGN 1, the number of SIGN -1 and subtract them.

Regards
Michel
Re: Subtract Positive values to negative values [message #403180 is a reply to message #403175] Thu, 14 May 2009 01:56 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Thanks for the reply ,Actually 5 and 2 gives 3 not 4,sorry for wrong calculation.
Re: Subtract Positive values to negative values [message #403196 is a reply to message #403180] Thu, 14 May 2009 02:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Whereas looking at the data you posted indicates that for product 10 there are 4 positive and 2 negative values - so which is it.

What you need for this is something like:
 SUM(CASE WHEN val > 0 THEN 1 ELSE 0 END)
,SUM(CASE WHEN val < 0 THEN 1 ELSE 0 END)
,SUM(CASE WHEN val > 0 THEN 1 WHEN VAL < 0 THEN -1 ELSE 0 END)

[EDITED by LF: fixed missing code tags]

[Updated on: Thu, 14 May 2009 04:40] by Moderator

Report message to a moderator

Re: Subtract Positive values to negative values [message #403401 is a reply to message #403175] Fri, 15 May 2009 06:15 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Dear All

First of all thank you very much for your support,I have solved my query with the help of your people.now i have done my work and using the following query and achievd my goal.

	select b.prod_cd , 
	initcap(b.prod_nm) , 
	sum(case when a.exc_st_amt>0 then 1 
	when exc_st_amt<0 then -1 
	else 0 end) Tot,
	to_char(inv_dt,'MON') Mon,to_char(a.INV_DT,'RRRR') Year,
	to_char(inv_dt,'MMRRRR') Mon2
	from SMK_USER.sl_inv_view a,smk_user.sl_prod b
		where to_date(inv_dt,'dd-mm-rrrr') between to_date(nvl(:inv_dt1 , inv_dt),'dd-mm-rrrr') and to_date(nvl(:inv_dt2 , inv_dt),'dd-mm-rrrr')
		and a.prod_cd=b.prod_cd
		group by b.prod_cd,b.prod_nm,to_char(inv_dt,'MON'),to_char(a.INV_DT,'RRRR') ,to_char(inv_dt,'MMRRRR')

Re: Subtract Positive values to negative values [message #403402 is a reply to message #403175] Fri, 15 May 2009 06:18 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
formated query copy is



SELECT   b.prod_cd, 
         Initcap(b.prod_nm), 
         Sum(CASE 
               WHEN a.exc_st_amt > 0 THEN 1 
               WHEN exc_st_amt < 0 THEN -1 
               ELSE 0 
             END) tot, 
         To_char(inv_dt,'MON')    mon, 
         To_char(a.inv_dt,'RRRR') YEAR, 
         To_char(inv_dt,'MMRRRR') mon2 
FROM     smk_user.sl_inv_view a, 
         smk_user.sl_prod b 
WHERE    To_date(inv_dt,'dd-mm-rrrr') BETWEEN To_date(Nvl(:inv_dt1,inv_dt),'dd-mm-rrrr') AND To_date(Nvl(:inv_dt2,inv_dt),'dd-mm-rrrr') 
         AND a.prod_cd = b.prod_cd 
GROUP BY b.prod_cd, 
         b.prod_nm, 
         To_char(inv_dt,'MON'), 
         To_char(a.inv_dt,'RRRR'), 
         To_char(inv_dt,'MMRRRR') 


Re: Subtract Positive values to negative values [message #403415 is a reply to message #403402] Fri, 15 May 2009 07:52 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
gozuhair wrote on Fri, 15 May 2009 07:18



         To_char(inv_dt,'MON')    mon, 
...
WHERE    To_date(inv_dt,'dd-mm-rrrr') BETWEEN 



One of these is wrong.
Previous Topic: Need help on changing all number in once
Next Topic: count hours between a date range
Goto Forum:
  


Current Time: Fri Dec 09 13:20:00 CST 2016

Total time taken to generate the page: 0.10902 seconds