Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2007
    Posts
    6

    Unanswered: sum () Over ( Partition by )

    Hello
    Is there in option in SYBASE ASE's T-sql to obtain sum of columns over window formed by partitooning table.

    In Oracle we could write it as

    SELECT SUM(Column1) over ( Partition BY Column 2) from dual;

    But same query give error in SYBASE ASE.

    Sybase manulas say Sybase IQ support such query.
    Please suggest some alternative.

  2. #2
    Join Date
    Jun 2004
    Posts
    115
    you need to do it t-sql using multiple temp tables

  3. #3
    Join Date
    Sep 2007
    Posts
    6
    How ? Could you please explain a bit more. In oracle query is as below

    Select rpt_maj_cat , sum(sum(mkt_ord_cnt)) over ( partition by rpt_maj_cat) from Ac1_6 where qtr=3 group by rpt_maj_cat,venue

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Can you give some sample data and the expected output
    Why do you have sum(sum seems unnecessary
    Your grouping seem inconsistent - venue is not in the select list

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Maybe this example can help you
    Code:
    create table #testdata (DEPNO int, ENAME varchar(20), SAL int)
    insert into #testdata select /*
    DEPNO   ENAME             SAL 
    ------  ---------- ---------- */
        10,'MILLER'   ,      1300   union all select
        10,'CLARK '   ,      2450   union all select
        10,'KING  '   ,      5000   union all select
        20,'SMITH '   ,       800   union all select
        20,'ADAMS '   ,      1100   union all select
        20,'JONES '   ,      2975   union all select
        20,'FORD  '   ,      3000   union all select
        20,'SCOTT '   ,      3000   union all select
        30,'JAMES '   ,       950   union all select
        30,'MARTIN'   ,      1250   union all select
        30,'WARD  '   ,      1250   union all select
        30,'TURNER'   ,      1500   union all select
        30,'ALLEN '   ,      1600   union all select
        30,'BLAKE '   ,      2850
    
    select a.*, b.DEPSAL
    from #testdata a
    join (select DEPNO, sum(SAL)DEPSAL from #testdata group by DEPNO) b
      on a.DEPNO=b.DEPNO
    
    drop table #testdata
    
    DEPNO       ENAME                SAL         DEPSAL      
    ----------- -----                ----------- ----------- 
             10 MILLER                      1300        8750 
             10 CLARK                       2450        8750 
             10 KING                        5000        8750 
             20 SMITH                        800       10875 
             20 ADAMS                       1100       10875 
             20 JONES                       2975       10875 
             20 FORD                        3000       10875 
             20 SCOTT                       3000       10875 
             30 JAMES                        950        9400 
             30 MARTIN                      1250        9400 
             30 WARD                        1250        9400 
             30 TURNER                      1500        9400 
             30 ALLEN                       1600        9400 
             30 BLAKE                       2850        9400 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •