The `OVER` clause specifies the partitioning, ordering and window &quot;over which&quot; the analytic function operates.

**Example #1: calculate a moving average**

    AVG(amt) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

    date   amt   avg_amt
    =====  ====  =======
    1-Jan  10.0  10.5
    2-Jan  11.0  17.0
    3-Jan  30.0  17.0
    4-Jan  10.0  18.0
    5-Jan  14.0  12.0

It operates over a moving window (3 rows wide) over the rows, ordered by date.

**Example #2: calculate a running balance**

    SUM(amt) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

    date   amt   sum_amt
    =====  ====  =======
    1-Jan  10.0  10.0
    2-Jan  11.0  21.0
    3-Jan  30.0  51.0
    4-Jan  10.0  61.0
    5-Jan  14.0  75.0

It operates over a window that includes the current row and all prior rows.

Note: for an aggregate with an `OVER` clause specifying a sort `ORDER`, the default window is `UNBOUNDED PRECEDING` to `CURRENT ROW`, so the above expression may be simplified to, with the same result:

    SUM(amt) OVER (ORDER BY date)

**Example #3: calculate the maximum within each group**

    MAX(amt) OVER (PARTITION BY dept)

    dept  amt   max_amt
    ====  ====  =======
    ACCT   5.0   7.0
    ACCT   7.0   7.0
    ACCT   6.0   7.0
    MRKT  10.0  11.0
    MRKT  11.0  11.0
    SLES   2.0   2.0

It operates over a window that includes all rows for a particular dept.

SQL Fiddle: http://sqlfiddle.com/#!4/9eecb7d/122

You can use it to transform some aggregate functions into analytic:

    SELECT  MAX(date)
    FROM    mytable

will return `1` row with a single maximum,


    SELECT  MAX(date) OVER (ORDER BY id)
    FROM    mytable

will return all rows with a running maximum.

It&#39;s part of the [Oracle analytic functions][1].


  [1]: http://www.orafaq.com/node/55

Another way to use OVER is to have a result column in your select operate on another &quot;partition&quot;, so to say.

This:

    SELECT 
        name, 
        ssn, 
        case 
          when ( count(*) over (partition by ssn) ) &gt; 1      
          then 1
          else 0
        end AS hasDuplicateSsn
    FROM table;

returns 1 in hasDuplicateSsn for each row whose ssn is shared by another row. Great for making &quot;tags&quot; for data for different error reports and such.

I am trying to convert from an int to a string but I am having trouble.  I followed the execution through the debugger and the string &#39;myT&#39; gets the value of &#39;sum&#39; but the &#39;if&#39; statement does not work correctly if the &#39;sum&#39; is 10,11,12.  Should I not be using a primitive int type to store the number?  Also, both methods I tried (see commented-out code) fail to follow the true path of the &#39;if&#39; statement.  Thanks!

    int x = [my1 intValue]; 
		int y = [my2 intValue]; 
		int sum = x+y;
		//myT = [NSString stringWithFormat:@&quot;%d&quot;, sum];
		myT = [[NSNumber numberWithInt:sum] stringValue];
		
		if(myT==@&quot;10&quot; || myT==@&quot;11&quot; || myT==@&quot;12&quot;)
			action = @&quot;numGreaterThanNine&quot;;


How to convert from int to string in objective c: example code

    SELECT 
        sum(TotalHoursM)
              + (TotalHoursT)
              + (TotalHoursW)
              + (TotalHoursTH)
              + (TotalHoursF) 
              AS TOTAL
    FROM LeaveRequest


SQL: sum 3 columns when one column has a null value?

What is the meaning of the OVER clause in Oracle?

OVER clause in Oracle

<p>What is the meaning of the OVER clause in Oracle?</p>


How can I get the number of rows affected by an UPDATE query in a Stored Procedure (SQL Server 2005), as a resultset. e.g.

    CREATE PROCEDURE UpdateTables
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

        UPDATE Table1 Set Column = 0 WHERE Column IS NULL
        UPDATE Table2 Set Column = 0 WHERE Column IS NULL
        UPDATE Table3 Set Column = 0 WHERE Column IS NULL
        UPDATE Table4 Set Column = 0 WHERE Column IS NULL
    END

Then return:

    Table1    Table2    Table3    Table4
    32        45        0         3

Return number of rows affected by UPDATE statements

I need to calculate the number of *FULL* month in SQL, i.e.

- 2009-04-16 to 2009-05-15 =&gt; 0 full month
- 2009-04-16 to 2009-05-16 =&gt; 1 full month
- 2009-04-16 to 2009-06-16 =&gt; 2 full months

I tried to use DATEDIFF, i.e.

    SELECT DATEDIFF(MONTH, &#39;2009-04-16&#39;, &#39;2009-05-15&#39;)

but instead of giving me full months between the two date, it gives me the difference of the month part, i.e.

    1

anyone know how to calculate the number of full months in SQL Server?

Calculating number of full months between two dates in SQL

How can I define a composite primary key consisting of two fields in SQL?

I am using PHP to create tables and everything. I want to make a table name `voting` with fields `QuestionID`, `MemeberID`, and `vote`. And the Composite primary key consists of the fields `QuestionID` and `MemberID`. 

How should I do this?


How can I define a composite primary key in SQL?

If I have a date 01/01/2009, I want to find out what day it was e.g. Monday, Tuesday, etc...

Is there a built-in function for this in SQL Server 2005/2008? Or do I need to use an auxiliary table?

Get day of week in SQL Server 2005/2008

What are the performance gains/losses between hash joins and merge joins, specifically in Oracle RDBMS?

What is the difference between a hash join and a merge join (Oracle RDBMS )?

I&#39;m using Oracle for development.  The password for a bootstrap account that I always use to rebuild my database has expired.

How do I turn off password expiration for this user (and all other users) permanently?

I&#39;m using Oracle 11g, which has passwords expire by default.

How do I turn off Oracle password expiration?

I have a table as follows:

    Filename - varchar
    Creation Date - Date format dd/mm/yyyy hh24:mi:ss
    Oldest cdr date - Date format dd/mm/yyyy hh24:mi:ss

How can I calcuate the difference in hours minutes and seconds (and possibly days) between the two dates in Oracle SQL?

Thanks



Calculate difference between 2 date / times in Oracle SQL

I need to add the current year as a variable in an SQL statement, how can I retrieve the current year using SQL?

i.e.
&lt;pre&gt;
  BETWEEN 
    TO_DATE(&#39;01/01/&lt;i&gt;**currentYear**&lt;/i&gt; 00:00:00&#39;, &#39;DD/MM/YYYY HH24:MI:SS&#39;)
    AND
    TO_DATE(&#39;31/12/&lt;i&gt;**currentYear**&lt;/i&gt; 23:59:59&#39;, &#39;DD/MM/YYYY HH24:MI:SS&#39;)
&lt;/pre&gt;

How do I get the current year using SQL on Oracle?

While attempting to execute SQL insert statements using [Oracle SQL Developer][1] I keep generating an &quot;Enter substitution value&quot; prompt:

    insert into agregadores_agregadores 
    (
     idagregador,
     nombre,
     url
    ) 
    values 
    (
     2,
     &#39;Netvibes&#39;,
     &#39;http://www.netvibes.com/subscribe.php?type=rss\&amp;amp;url=&#39;
    );
I&#39;ve tried [escaping the special character in the query][2] using the &#39;\&#39; above but I still can&#39;t avoid the ampersand, &#39;&amp;&#39;, causing a string substitution.


  [1]: http://www.oracle.com/technology/products/database/sql_developer/index.html
  [2]: http://download.oracle.com/docs/cd/B10501_01/text.920/a96518/cqspcl.htm

Oracle SQL escape character (for a &#39;&amp;&#39;)

I&#39;ve been using `GROUP BY` for all types of aggregate queries over the years.  Recently, I&#39;ve been reverse-engineering some code that uses `PARTITION BY` to perform aggregations.  In reading through all the documentation I can find about `PARTITION BY`, it sounds a lot like `GROUP BY`, maybe with a little extra functionality added in?  Are they two versions of the same general functionality, or are they something different entirely?

SQL Server: Difference between PARTITION BY and GROUP BY

Here&#39;s my code:

    select yr,count(*)
    from movie
    join casting on casting.movieid=movie.id
    join actor on casting.actorid = actor.id
    where actor.name = &#39;John Travolta&#39;
    group by yr;

Here&#39;s the question:

&gt; Which were the busiest years for &#39;John Travolta&#39;. Show the number of movies he made for each year.

Here&#39;s the table structure:

    movie(id, title, yr, score, votes, director)
    actor(id, name)
    casting(movieid, actorid, ord)

This is the output I am getting:

    yr	    count(*)
    1976	1
    1977	1
    1978	1
    1981	1
    1994	1
    -- etc.

I need to get the rows for which `count(*)` is max.
How do I do this?


Can I do a max(count(*)) in SQL?

When I have a column with separated values, I can use the `unnest()` function:

    myTable
    id | elements
    ---+------------
    1  |ab,cd,efg,hi
    2  |jk,lm,no,pq
    3  |rstuv,wxyz
    
    select id, unnest(string_to_array(elements, &#39;,&#39;)) AS elem
    from myTable
    
    id | elem
    ---+-----
    1  | ab
    1  | cd
    1  | efg
    1  | hi
    2  | jk
    ...

How can I include element numbers? I.e.:

    id | elem | nr
    ---+------+---
    1  | ab   | 1
    1  | cd   | 2
    1  | efg  | 3
    1  | hi   | 4
    2  | jk   | 1
    ...

I want the *original position* of each element in the source string. I&#39;ve tried with window functions (`row_number()`, `rank()` etc.) but I always get `1`. Maybe because they are in the same row of the source table?

I know it&#39;s a bad table design. It&#39;s not mine, I&#39;m just trying to fix it.

PostgreSQL unnest() with element number

What&#39;s the difference between `RANK()` and `DENSE_RANK()` functions? How to find out nth salary in the following `emptbl` table?

    DEPTNO	EMPNAME	   SAL
    ------------------------------
    10	     rrr	10000.00
    11	     nnn	20000.00
    11	     mmm	5000.00
    12	     kkk	30000.00
    10	     fff	40000.00
    10	     ddd	40000.00
    10	     bbb	50000.00
    10	     ccc	50000.00



If in the table data having `nulls`, what will happen if I want to find out `nth` salary?







What&#39;s the difference between RANK() and DENSE_RANK() functions in oracle?

How to select row number in postgres.

I tried this:

    select
        row_number() over (ORDER BY cgcode_odc_mapping_id)as rownum,
        cgcode_odc_mapping_id
      from access_odc.access_odc_mapping_tb
      order by cgcode_odc_mapping_id

and got this error:

&lt;pre&gt;
ERROR: syntax error at or near &quot;over&quot;
LINE 1: select row_number() over (ORDER BY cgcode_odc_mapping_id)as
&lt;/pre&gt;

I have checked these pages : https://stackoverflow.com/questions/3397121/how-to-show-row-numbers-in-postgresql-query


---

  This is my query:

     select row_number() over (ORDER BY cgcode_odc_mapping_id)as rownum,cgcode_odc_mapping_id from access_odc.access_odc_mapping_tb order by cgcode_odc_mapping_id 

this is the error: 

ERROR: syntax error at or near &quot;over&quot; LINE 1: select row_number() over (ORDER BY cgcode_odc_mapping_id)as

Content Type	Original Author	Original Content on Stackoverflow
Question	paweloque	View Question on Stackoverflow
Solution 1 - Sql	Jeffrey Kemp	View Answer on Stackoverflow
Solution 2 - Sql	Quassnoi	View Answer on Stackoverflow
Solution 3 - Sql	cletus	View Answer on Stackoverflow
Solution 4 - Sql	stobix	View Answer on Stackoverflow

OVER clause in Oracle

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

Solution 2 - Sql

Solution 3 - Sql

Solution 4 - Sql

SQL: sum 3 columns when one column has a null value?

How to convert from int to string in objective c: example code

Attributions