1 Reply Latest reply: Jul 10, 2013 6:20 PM by Frank Kulash RSS

    Analytical Function - COUNT issue

    Nalla(Nallasivam)

      Hello All,

       

      DB Oracle 11g running on Linux x86 64-bit platform ..

       

      I'm curious to know why the below two sets of queries produce different reults.

       

      Q 1

      ------

       

      WITH TEMP AS

      (

      SELECT 'ONE' VAL, 5 PAR FROM DUAL UNION ALL

      SELECT 'ONE' VAL, 7 PAR FROM DUAL UNION ALL

      SELECT 'FIVE' VAL, 17 PAR FROM DUAL UNION ALL

      SELECT 'FOUR' VAL, 0 PAR FROM DUAL UNION ALL

      SELECT 'THREE' VAL, 78 PAR FROM DUAL UNION ALL

      SELECT 'THREE' VAL, 34 PAR FROM DUAL UNION ALL

      SELECT 'THREE' VAL, -4 PAR FROM DUAL UNION ALL

      SELECT 'TWO' VAL, 6 PAR FROM DUAL UNION ALL

      SELECT 'TWO' VAL, 12 PAR FROM DUAL 

      )

      SELECT DISTINCT SUM(PAR) OVER(PARTITION BY VAL), VAL CNT FROM TEMP;

       

       

       

      Q2

      ----

      WITH TEMP AS

      (

      SELECT 'ONE' VAL, 5 PAR FROM DUAL UNION ALL

      SELECT 'ONE' VAL, 7 PAR FROM DUAL UNION ALL

      SELECT 'FIVE' VAL, 17 PAR FROM DUAL UNION ALL

      SELECT 'FOUR' VAL, 0 PAR FROM DUAL UNION ALL

      SELECT 'THREE' VAL, 78 PAR FROM DUAL UNION ALL

      SELECT 'THREE' VAL, 34 PAR FROM DUAL UNION ALL

      SELECT 'THREE' VAL, -4 PAR FROM DUAL UNION ALL

      SELECT 'TWO' VAL, 6 PAR FROM DUAL UNION ALL

      SELECT 'TWO' VAL, 12 PAR FROM DUAL 

      )

      SELECT DISTINCT SUM(PAR) OVER(PARTITION BY VAL ORDER BY PAR) CNT, VAL FROM TEMP;

       

      The only difference between the 2 queries is Q2 has the 'ORDER BY' extra.

       

      For Q2, I was assuming the partitions are ordered by PAR and the SUM is applied over the partition. Obviously, the results prove my assumption is wrong.

       

      So, Q2 is just as good as select * from temp;

        • 1. Re: Analytical Function - COUNT issue
          Frank Kulash

          Hi,

           

          You're right about "ORDER BY par"; it means that the SUM will be from the lowest value of par up to and including the current row.

          I think you're ignoring "PARTITION BY val", which means every distinct value of val will be a world unto itself, not affected by any rows with a different value of val.  Since val is different on every row, that means the SUM shown will always be the SUM of just that 1 item, so SUM (par) will indeed be the same as par.

          Try it without the PARTITION BY clause.