[Webinar] Streamline your web hosting managementRegister Today

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 78

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:
sujith80 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

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

612 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