Home » SQL & PL/SQL » SQL & PL/SQL » How to code a SQL for offset data from single table
icon10.gif  How to code a SQL for offset data from single table [message #220066] Sun, 18 February 2007 09:35 Go to next message
Smith_X
Messages: 56
Registered: January 2007
Member
CODE         INPUT         OUTPUT
140003       10,836        10,719  
140004       10,863        10,471  
140005       10,716        10,790  
140009       10,037        10,738  
140002       10,305        10,824  
140010       10,986        10,139  
140010       10,095        10,317  
140007       10,292        10,632  
140001       10,614        10,499  
140008       10,661        10,867  
140002       10,021        10,815  
140001       10,878        10,047  
140001       10,176        10,029  
140000       10,627        10,721  
140001       10,632        10,454  
140009       10,722        10,693  
140006       10,801        10,217  
140000       10,823        10,185  
140003       10,262        10,584  
140003       10,548        10,335  
140007       10,019        10,397  
140009       10,955        10,823  
140006       10,422        10,705  
140010       10,667        10,440  
140006       10,277        10,098


Hello you guys,

I have very complicated question that I had spent many hour to solve this problem but I think it's beyond my ability to solve it my myself.

Assume that my table have data as the example above. I want to query the table by..

1. SUM (INPUT) and SUM(OUTPUT)
2. Group by CODE Column
3. If sum(input) > sum(output) then, use sum(input) - sum(output) and show this result in INPUT column.. leave the OUTPUT column as 0. And vice versa, If sum(output) > sum(input) then use sum(output) - sum(input) and show this result in OUTPUT column.. leave the INPUT column as 0

I can do the 1. and 2. but I can't even imagine how do I can create 3.

Please advise me.

The result may look like below;

CODE         INPUT         OUTPUT
140001            0        20,441  
140002            0        31,272  
140003       20,716             0  
140004        9,037             0  
140005            0         4,127  
140006        1,986             0  
...             ...           ...
Re: How to code a SQL for offset data from single table [message #220068 is a reply to message #220066] Sun, 18 February 2007 09:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You didn't define what to do if sum(input) = sum(output), but here it is:

SQL> drop table faq;

Table dropped.

SQL> create table faq  (CODE number(6), INPUT number(6), OUTPUT number(6));

Table created.

SQL> insert all
  2  into faq values(140003, 10836, 10719)
  3  into faq values(140004, 10863, 10471)
  4  into faq values(140005, 10716, 10790)
  5  into faq values(140009, 10037, 10738)
  6  into faq values(140002, 10305, 10824)
  7  into faq values(140010, 10986, 10139)
  8  into faq values(140010, 10095, 10317)
  9  into faq values(140007, 10292, 10632)
 10  into faq values(140001, 10614, 10499)
 11  into faq values(140008, 10661, 10867)
 12  into faq values(140002, 10021, 10815)
 13  into faq values(140001, 10878, 10047)
 14  into faq values(140001, 10176, 10029)
 15  into faq values(140000, 10627, 10721)
 16  into faq values(140001, 10632, 10454)
 17  into faq values(140009, 10722, 10693)
 18  into faq values(140006, 10801, 10217)
 19  into faq values(140000, 10823, 10185)
 20  into faq values(140003, 10262, 10584)
 21  into faq values(140003, 10548, 10335)
 22  into faq values(140007, 10019, 10397)
 23  into faq values(140009, 10955, 10823)
 24  into faq values(140006, 10422, 10705)
 25  into faq values(140010, 10667, 10440)
 26  into faq values(140006, 10277, 10098)
 27  select * from dual;

25 rows created.

SQL> 
SQL> select code
  2  ,	    decode(sign(sum(input - output)),  1, sum(input-output), 0) input
  3  ,	    decode(sign(sum(input - output)), -1, sum(output-input), 0) output
  4  from   faq
  5  group  by code
  6  order  by code;

      CODE      INPUT     OUTPUT
---------- ---------- ----------
    140000        544          0
    140001       1271          0
    140002          0       1313
    140003          8          0
    140004        392          0
    140005          0         74
    140006        480          0
    140007          0        718
    140008          0        206
    140009          0        540
    140010        852          0

11 rows selected.

Strangely enough I get TOTALLY different values then you. Is it because I misunderstood your requirements or did you just make up some output?
Re: How to code a SQL for offset data from single table [message #220071 is a reply to message #220068] Sun, 18 February 2007 10:43 Go to previous messageGo to next message
Smith_X
Messages: 56
Registered: January 2007
Member
W~O~N~D~E~R~F~U~L...

Thank you very much, Frank! I'm really appriciate for your help.

That's what I expected for.. WOW.. One more thing, the result that you get is far different from mine because those data is just an sample (I random result to make an example what I expected for..) So, you are correct Smile

In case that if INPUT = OUTOUT then, I will leave both INPUT and OUTPUT are zero number. I think I will replicate the first expression but use sign check for 0 instead.

OK, now I need to learn how to use SIGN and DECODE more and more..

Thank you very much

Re: How to code a SQL for offset data from single table [message #220074 is a reply to message #220071] Sun, 18 February 2007 11:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Don't get too hung on decode. If you are not used to using it, maybe you better get used to using case. (I'm an oldtimer, decode is burned into my bones; case wasn't around yet Wink)
Case is way easier to read and provides for more then two options, as opposed to decode.

SQL> select code
  2  ,      case sign(sum(input - output))
  3              when 1 then sum(input-output)
  4              else 0
  5         end as input
  6  ,      case sign(sum(input - output))
  7              when -1 then sum(output-input)
  8              else 0
  9         end as output
 10  from   faq
 11  group  by code
 12  order  by code;

      CODE      INPUT     OUTPUT
---------- ---------- ----------
    140000        544          0
    140001       1271          0
    140002          0       1313
    140003          8          0
    140004        392          0
    140005          0         74
    140006        480          0
    140007          0        718
    140008          0        206
    140009          0        540
    140010        852          0

11 rows selected.
Re: How to code a SQL for offset data from single table [message #220079 is a reply to message #220066] Sun, 18 February 2007 12:23 Go to previous messageGo to next message
Smith_X
Messages: 56
Registered: January 2007
Member
Dear Frank,

Oh thank you very much once again.

You educated me a lot, Frank and I also learned a lot as well. Thank you very much for your reply. Very Happy

Actually, I understand only some basic SQL command. SELECT, FROM, WHERE or sorting ORDER BY and some function DISTINCT, MIN, MAX, COUNT oh and GROUP BY, too.

After I read some note about DECODE via Google, I regret to say that I can't even think about how to combine both DECODE and SIGN to create a proficient SQL Statement like this. It's really amazing~.

If you could, please recommend me a book or website that I can learn some more complex SQL code,?

Regards,
Smith
Re: How to code a SQL for offset data from single table [message #220080 is a reply to message #220079] Sun, 18 February 2007 13:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Hm, a website? How about this very site?
Start with the newbie forum and stick to that for a while, until you feel comfortable with sql.
Read the sticky (first post) of the newbie forum for some more links. Try to get familiar with the manuals.
I don't know any good books for beginners/intermediates; maybe somebody else knows?
Re: How to code a SQL for offset data from single table [message #220651 is a reply to message #220066] Wed, 21 February 2007 08:40 Go to previous messageGo to next message
Smith_X
Messages: 56
Registered: January 2007
Member
Excuse me,
I have further question.

If I want to include the case when both input and output are tie but I do not want to add new column. I mean if both input and output are tie, then just leave both input and output as zero.

well, the problem is that if I add the third case (with sign zero) then, I will have 3 columns (which I do not want it.)

Any advice would be great please...
Re: How to code a SQL for offset data from single table [message #220666 is a reply to message #220651] Wed, 21 February 2007 09:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why would that mean another column?
Add another when to both case-statements
Re: How to code a SQL for offset data from single table [message #220712 is a reply to message #220066] Wed, 21 February 2007 12:21 Go to previous messageGo to next message
Smith_X
Messages: 56
Registered: January 2007
Member
select code
, decode(sign(sum(input - output)),  1, sum(input-output), sign(sum(input - output)),  0, sum(input-output), 0) input
, decode(sign(sum(input - output)), -1, sum(output-input), sign(sum(input - output)),  0, sum(output-input), 0) output
from   faq
group  by code
order  by code;


Do I understand correct please?? (the machine that I currently using at this moment does not have Oracle and tables..)

[Updated on: Wed, 21 February 2007 13:03]

Report message to a moderator

Re: How to code a SQL for offset data from single table [message #220810 is a reply to message #220712] Thu, 22 February 2007 04:07 Go to previous message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
gr8
Previous Topic: Calling stored procedure from excel
Next Topic: difference between Performance Tuning & Optimization
Goto Forum:
  


Current Time: Sun Dec 04 08:41:27 CST 2016

Total time taken to generate the page: 0.10071 seconds