Improve company productivity with a Business Account.Sign Up

x
?
Solved

Oracle sum question

Posted on 2012-03-26
4
Medium Priority
?
524 Views
Last Modified: 2012-06-21
Having table TestTB (dates in dd-mm-yyyy)

Item;Date;qty
A;12-01-2012;5
A;27-01-2012;-2
A;04-02-2012;1
B;08-11-2011;-1
B;08-11-2011;3
B;15-11-2011;7
C;17-01-2012;-5
C;25-02-2012;3

I need A Select Sum pr. month-Year - summarized (Balance)

Item;Mont-;Balance
A;01-2012;3
A;02-2012;4         (that is 3 from 01 and 1 from 02)
B;11-2011;9
C;01-2012;-5
C;02-2012;-2       (that is -5 from 01 and 3 from 02)

I use Oracle 10g
0
Comment
Question by:Vinum
4 Comments
 
LVL 79

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37765495
I don't see how you get itemA numbers but based on what I think you want, something like:  

select item, to_char(date_col,'MM-YYYY'), sum(qty) from table
group by item, to_char(date_col,'MM-YYYY');
0
 

Author Comment

by:Vinum
ID: 37765575
Hi

It is not just a sum pr. month - it is a balance.

Example with item A:
The sum/balance for 01-2012 is 3 (5 and -2)
The sum/balance for 02-2012 is 4 (1 for month 02 and 3 for months 01)


The idea is to make a balance over time.
Another example:
I have 5 of X on stock.
In January I sell 2, balance in the end of January is 3 (5-2)
In February I sell 1 and buy 10, balance at the end of February is 12 (5-2-1+10)
In March I sell 5, balance at the end of March i 7 (5-2-1+10-5)
and so on....

X;01-2012;3
X;02-2012;12
X;03-2012;7
.
.
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 37765937
I think you can only do that with a subselect:

select distinct tb1.item, 
   to_char(tb1.dat, 'mm-yyyy') per, 
   (select sum(qty) 
    from testtb tb2 
    where tb2.item = tb1.item and 
      to_char(tb2.dat, 'yyyymm') <= to_char(tb1.dat, 'yyyymm')) balance 
from testtb tb1 
group by tb1.item, tb1.dat 
order by tb1.item, per;

Open in new window

0
 
LVL 27

Accepted Solution

by:
Sujith earned 2000 total points
ID: 37766297
See the below, combine a group by with analytical function -
SQL> select *
  2  from tbl1;

ITEM                 DT                 QTY
-------------------- ----------- ----------
A                    12-jan-2012          5
A                    27-jan-2012         -2
A                    04-feb-2012          1
B                    08-nov-2011         -1
B                    08-nov-2011          3
B                    15-nov-2011          7
C                    17-jan-2012         -5
C                    25-feb-2012          3

8 rows selected.

SQL>
SQL> select item, dt month, sum(qty) over( partition by item order by to_date(dt, 'mm-yyyy')) balance
  2  from (
  3  select item, to_char(dt, 'mm-yyyy') dt, sum(qty) qty
  4  from tbl1
  5  group by item, to_char(dt, 'mm-yyyy')
  6  )
  7  order by 1,2;

ITEM                 MONTH      BALANCE
-------------------- ------- ----------
A                    01-2012          3
A                    02-2012          4
B                    11-2011          9
C                    01-2012         -5
C                    02-2012         -2

SQL>

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question