Solved

Oracle sum question

Posted on 2012-03-26
4
472 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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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
Comment Utility
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:
sujith80 earned 500 total points
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now