Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Query output for group by

USER101Mar 19 2009 — edited Nov 3 2010

Hi !

Can someone help ?
What I am looking for is, those days in the first query which do not return any rows should be displayed with a 0. I have all the days available in that table. Ie. 13/14/15th it should display as 0 instead of skipping it

SQL>  select trunc(logtimestamp),count(*)
from SOACOE_LOGGER_SUMMARY
where
   ERRORTYPE is not null
 group by trunc(logtimestamp)
 order by trunc(logtimestamp);  2    3    4    5    6

TRUNC(LOGTI   COUNT(*)
----------- ----------
05-MAR-2009          2
06-MAR-2009         13
07-MAR-2009          6
08-MAR-2009          1
09-MAR-2009          3
10-MAR-2009          3
12-MAR-2009          1
17-MAR-2009         36
18-MAR-2009         28

9 rows selected.

SQL> select distinct trunc(logtimestamp)
from SOACOE_LOGGER_SUMMARY
 order by trunc(logtimestamp);   2    3

TRUNC(LOGTI
-----------
05-MAR-2009
06-MAR-2009
07-MAR-2009
08-MAR-2009
09-MAR-2009
10-MAR-2009
11-MAR-2009
12-MAR-2009
13-MAR-2009
14-MAR-2009
15-MAR-2009

TRUNC(LOGTI
-----------
16-MAR-2009
17-MAR-2009
18-MAR-2009
19-MAR-2009

15 rows selected.

Thanks

G

Comments

548849 
Why did you miss values for week_num 6 & 7 ? 

The result is 0 whereas i was expecting 5 & 10.

Could you also post some create and insert scripts of the table ?
548091
Hi,

Apologies for belated reply.

There's no missing values for wk_num 6 & 7.

Below is the part of the data as to how the 0 appears for negative values. The "Logical Running Sum" is not a physical column, but I am giving it below just for understanding, and using this logical column the Result column is having 0 for negative values.
WK_NUM	Product	Qty	Logical Running Sum	Result
4	A	-15	-15	                0
5	A	-20	-35	                0
6	A	10	-25	                0
7	A	5	-20	                0
8	A	50	30	                30
Same way it works for wk_num's 11 to 14 and wk_num's 15 to 16.

Looking forward to any solutions.

Thanks,
Satyaki_De
Whenever you post any problem please post the DDL & DML for that environment. Or,
WITH tab
AS
   (
       SELECT <column list> FROM <table name>
       UNION ALL
       SELECT <column list> FROM <table name>
   )
SELECT *
FROM tab;
This will help us to concentrate on your main problem rather preparing the environment first.

Got me?

Regards.

Satyaki De.
548091
Below is the script for the data which is in the Prod table:
with prod as (
    select 1 as wk_num, 'A' as prod, 0 as qty from dual union all
    select 2 as wk_num, 'A' as prod, 10 as qty from dual union all
    select 3 as wk_num, 'A' as prod, 20 as qty from dual union all
    select 4 as wk_num, 'A' as prod, -15 as qty from dual union all
    select 5 as wk_num, 'A' as prod, -20 as qty from dual union all
    select 6 as wk_num, 'A' as prod, 10 as qty from dual union all
    select 7 as wk_num, 'A' as prod, 5 as qty from dual union all
    select 8 as wk_num, 'A' as prod, 50 as qty from dual union all
    select 9 as wk_num, 'A' as prod, 15 as qty from dual union all
    select 10 as wk_num, 'A' as prod, 0 as qty from dual union all
    select 11 as wk_num, 'A' as prod, -10 as qty from dual union all
    select 12 as wk_num, 'A' as prod, -30 as qty from dual union all
    select 13 as wk_num, 'A' as prod, -40 as qty from dual union all
    select 14 as wk_num, 'A' as prod, 100 as qty from dual union all
    select 15 as wk_num, 'A' as prod, -20 as qty from dual union all
    select 16 as wk_num, 'A' as prod, 40 as qty from dual union all
    select 17 as wk_num, 'A' as prod, 35 as qty from dual union all
    select 18 as wk_num, 'A' as prod, 20 as qty from dual union all
    select 19 as wk_num, 'A' as prod, 10 as qty from dual
)    
select *
from prod;
hope the above helps.
Thanks,
Hoek
I think I'm missing one requirement here, on what do you want your running total exactly?
SQL> with prod as (
  2      select 1 as wk_num, 'A' as prod, 0 as qty from dual union all
  3      select 2 as wk_num, 'A' as prod, 10 as qty from dual union all
  4      select 3 as wk_num, 'A' as prod, 20 as qty from dual union all
  5      select 4 as wk_num, 'A' as prod, -15 as qty from dual union all
  6      select 5 as wk_num, 'A' as prod, -20 as qty from dual union all
  7      select 6 as wk_num, 'A' as prod, 10 as qty from dual union all
  8      select 7 as wk_num, 'A' as prod, 5 as qty from dual union all
  9      select 8 as wk_num, 'A' as prod, 50 as qty from dual union all
 10      select 9 as wk_num, 'A' as prod, 15 as qty from dual union all
 11      select 10 as wk_num, 'A' as prod, 0 as qty from dual union all
 12      select 11 as wk_num, 'A' as prod, -10 as qty from dual union all
 13      select 12 as wk_num, 'A' as prod, -30 as qty from dual union all
 14      select 13 as wk_num, 'A' as prod, -40 as qty from dual union all
 15      select 14 as wk_num, 'A' as prod, 100 as qty from dual union all
 16      select 15 as wk_num, 'A' as prod, -20 as qty from dual union all
 17      select 16 as wk_num, 'A' as prod, 40 as qty from dual union all
 18      select 17 as wk_num, 'A' as prod, 35 as qty from dual union all
 19      select 18 as wk_num, 'A' as prod, 20 as qty from dual union all
 20      select 19 as wk_num, 'A' as prod, 10 as qty from dual
 21  )    
 22  select wk_num
 23  ,      prod
 24  ,      qty
 25  ,      sum(qty) over (order by wk_num) running_total
 26  ,      case when sign(sum(qty) over (order by wk_num)) = -1 then 0
 27              else sum(qty) over (order by wk_num)
 28         end result
 29  from prod
 30  order by wk_num;

    WK_NUM P        QTY RUNNING_TOTAL     RESULT
---------- - ---------- ------------- ----------
         1 A          0             0          0
         2 A         10            10         10
         3 A         20            30         30
         4 A        -15            15         15
         5 A        -20            -5          0
         6 A         10             5          5
         7 A          5            10         10
         8 A         50            60         60
         9 A         15            75         75
        10 A          0            75         75
        11 A        -10            65         65
        12 A        -30            35         35
        13 A        -40            -5          0
        14 A        100            95         95
        15 A        -20            75         75
        16 A         40           115        115
        17 A         35           150        150
        18 A         20           170        170
        19 A         10           180        180

19 rows selected.
701278
Hi Mak2,

since your requirement is based on procedural rules, you could use the model clause like this:
SQL> WITH prod AS (
  2  SELECT 1 wk_num,'A' prod, 0 qty FROM dual UNION ALL
  3  SELECT 2 ,'A', 10 FROM dual UNION ALL
  4  SELECT 3 ,'A', 20 FROM dual UNION ALL
  5  SELECT 4 ,'A', -15 FROM dual UNION ALL
  6  SELECT 5 ,'A', -20 FROM dual UNION ALL
  7  SELECT 6 ,'A', 10 FROM dual UNION ALL
  8  SELECT 7 ,'A', 5 FROM dual UNION ALL
  9  SELECT 8 ,'A', 50 FROM dual UNION ALL
 10  SELECT 9 ,'A', 15 FROM dual UNION ALL
 11  SELECT 10 ,'A', 0 FROM dual UNION ALL
 12  SELECT 11 ,'A', -10 FROM dual UNION ALL
 13  SELECT 12 ,'A', -30 FROM dual UNION ALL
 14  SELECT 13 ,'A', -40 FROM dual UNION ALL
 15  SELECT 14 ,'A', 100 FROM dual UNION ALL
 16  SELECT 15 ,'A', -20 FROM dual UNION ALL
 17  SELECT 16 ,'A', 40 FROM dual UNION ALL
 18  SELECT 17 ,'A', 35 FROM dual UNION ALL
 19  SELECT 18 ,'A', 20 FROM dual UNION ALL
 20  SELECT 19 ,'A', 10 FROM dual
 21  ) SELECT wk_num, prod, qty, running_total, results FROM prod p
 22  MODEL
 23     PARTITION BY (prod)
 24     DIMENSION BY (wk_num)
 25     MEASURES (qty, 0 AS running_total, 0 AS results)
 26     RULES (
 27        running_total[ANY]=CASE
 28                             WHEN nvl(running_total[cv()-1], 0) >= 0 THEN qty[cv()]
 29                             ELSE nvl(running_total[cv()-1], 0) + qty[cv()]
 30                           END,
 31        results[ANY]=CASE
 32                       WHEN running_total[cv()] >= 0 THEN running_total[cv()]
 33                       ELSE 0
 34                     END
 35     );

    WK_NUM P        QTY RUNNING_TOTAL    RESULTS
---------- - ---------- ------------- ----------
         1 A          0             0          0
         2 A         10            10         10
         3 A         20            20         20
         4 A        -15           -15          0
         5 A        -20           -35          0
         6 A         10           -25          0
         7 A          5           -20          0
         8 A         50            30         30
         9 A         15            15         15
        10 A          0             0          0
        11 A        -10           -10          0

    WK_NUM P        QTY RUNNING_TOTAL    RESULTS
---------- - ---------- ------------- ----------
        12 A        -30           -40          0
        13 A        -40           -80          0
        14 A        100            20         20
        15 A        -20           -20          0
        16 A         40            20         20
        17 A         35            35         35
        18 A         20            20         20
        19 A         10            10         10

19 rows selected.
Regards,

--
Vincent
Satyaki_De
Perhaps you are looking for this ->
satyaki>
satyaki>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Elapsed: 00:00:00.00
satyaki>
satyaki>
satyaki>with prod as (
  2      select 1 as wk_num, 'A' as prod, 0 as qty from dual union all
  3      select 2 as wk_num, 'A' as prod, 10 as qty from dual union all
  4      select 3 as wk_num, 'A' as prod, 20 as qty from dual union all
  5      select 4 as wk_num, 'A' as prod, -15 as qty from dual union all
  6      select 5 as wk_num, 'A' as prod, -20 as qty from dual union all
  7      select 6 as wk_num, 'A' as prod, 10 as qty from dual union all
  8      select 7 as wk_num, 'A' as prod, 5 as qty from dual union all
  9      select 8 as wk_num, 'A' as prod, 50 as qty from dual union all
 10      select 9 as wk_num, 'A' as prod, 15 as qty from dual union all
 11      select 10 as wk_num, 'A' as prod, 0 as qty from dual union all
 12      select 11 as wk_num, 'A' as prod, -10 as qty from dual union all
 13      select 12 as wk_num, 'A' as prod, -30 as qty from dual union all
 14      select 13 as wk_num, 'A' as prod, -40 as qty from dual union all
 15      select 14 as wk_num, 'A' as prod, 100 as qty from dual union all
 16      select 15 as wk_num, 'A' as prod, -20 as qty from dual union all
 17      select 16 as wk_num, 'A' as prod, 40 as qty from dual union all
 18      select 17 as wk_num, 'A' as prod, 35 as qty from dual union all
 19      select 18 as wk_num, 'A' as prod, 20 as qty from dual union all
 20      select 19 as wk_num, 'A' as prod, 10 as qty from dual
 21  )    
 22  select k.wk_num,
 23         k.prod,
 24         k.qty,
 25         k.running_sal,
 26         case
 27           when running_sal < 0 
 28           or qty < 0 then
 29             0
 30         else
 31           qty
 32         end result
 33  from (
 34          select wk_num,
 35                 prod,
 36                 qty,
 37                 sum(qty) over(order by wk_num) running_sal
 38          from prod
 39       ) k;

    WK_NUM P        QTY RUNNING_SAL     RESULT
---------- - ---------- ----------- ----------
         1 A          0           0          0
         2 A         10          10         10
         3 A         20          30         20
         4 A        -15          15          0
         5 A        -20          -5          0
         6 A         10           5         10
         7 A          5          10          5
         8 A         50          60         50
         9 A         15          75         15
        10 A          0          75          0
        11 A        -10          65          0

    WK_NUM P        QTY RUNNING_SAL     RESULT
---------- - ---------- ----------- ----------
        12 A        -30          35          0
        13 A        -40          -5          0
        14 A        100          95        100
        15 A        -20          75          0
        16 A         40         115         40
        17 A         35         150         35
        18 A         20         170         20
        19 A         10         180         10

19 rows selected.

Elapsed: 00:00:00.01
satyaki>
Regards.

Satyaki De.
Aketi Jyuuzou
My model clause article (in Japanese)
http://www.geocities.jp/oraclesqlpuzzle/oow2009-olap-model.html

I used Least , greatest and model clause B-)

In model clause,
If runningSum is over than 0,
I will regard runningSum was 0 B-)

And Then I use greatest
when runningSum is negative,I will change runningSum is 0 ;-) 
create table modelRun(WK_NUM,Qty) as
select  1,  0 from dual union
select  2, 10 from dual union
select  3, 20 from dual union
select  4,-15 from dual union
select  5,-20 from dual union
select  6, 10 from dual union
select  7,  5 from dual union
select  8, 50 from dual union
select  9, 15 from dual union
select 10,  0 from dual union
select 11,-10 from dual union
select 12,-30 from dual union
select 13,-40 from dual union
select 14,100 from dual union
select 15,-20 from dual union
select 16, 40 from dual union
select 17, 35 from dual union
select 18, 20 from dual union
select 19, 10 from dual;

select WK_NUM,Qty,greatest(runSum,0) as Result
  from modelRun
 model
dimension by(WK_NUM)
measures(Qty,0 as runSum)
rules(
runSum[any] order by WK_NUM =
 Least(presentv(runSum[cv()-1],runSum[cv()-1],0),0)
+Qty[cv()]);

WK_NUM  QTY  RESULT
------  ---  ------
     1    0       0
     2   10      10
     3   20      20
     4  -15       0
     5  -20       0
     6   10       0
     7    5       0
     8   50      30
     9   15      15
    10    0       0
    11  -10       0
    12  -30       0
    13  -40       0
    14  100      20
    15  -20       0
    16   40      20
    17   35      35
    18   20      20
    19   10      10
1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 17 2009
Added on Mar 19 2009
21 comments
1,419 views