We have a DB2 version 9.5 database and I am trying to write an analytic function to do a running total count:

select {field1},{field2}, count(*) over(partition by ) as CNT
from database.table
where date({field2}) = '2010-02-01'

I get an error: SQL0104N An unexpected token "(" was found following ""...SQLSTATE=42601.

I dont know why an error is occuring here, its a simple analytic funtion, please advise if any thoughts on this. Thanks!.

partition by what?

partition by {field2}

Not sure understand your problem correctly. Do you not just need a "GROUP BY":

select field1,field2, count(1) total
from tablex
where field2 = '2010-02-01'
GROUP BY field1,field2

Ok. Does anybody know how to write an analytic function in DB2? What I am trying to get is a running total,

select {field1},{field2}, count(*) over(partition by {field2}) as CNT
from database.table
where date({field2}) = '2010-02-01'

The above query is not working...can someone who has a DB2 databse test a query similar to the above and see if it runs or not? I want to see why its not working. Thanks.

Do you have any sample data (a few rows is fine) for field1 and field2?

Additionally, and more importantly, what do you expect to see after your query has been run (assuming it was working).

THIS should establish your exact requirements.

Ok. Here is some sample data, but this time i will use a date range in the SQL.

field1

1. 088213
2. 05849
3. 23289
4. 89890
5. 283092
6. 236922


field2

1. 2011-01-02
2 2011-01-15
3. 2011-02-20
4. 2011-04-14
5. 2011-05-23
6. 2011-05-30

I want to see a total count of field1 within the date range.

select {field1},{field2}, count(*) over(partition by {field2}) as CNT
from database.table
where date({field2}) >= '2010-01-01' and
date{field2} <= '2011-05-30'


field 1 field2 CNT

1. 088213 2011-01-02 1
2. 05849 2011-01-15 1
3. 23289 2011-02-20 1
4. 89890 2011-04-14 1
5. 283092 2011-05-23 1
6. 236922 2011-05-30 1

Depending on what the result wille be, the CNT result is just an assumption, but I just want to see the Analytic function work in DB2


Thanks.

This is a bad sample data(or test data).
Because there were no duplication in field2, so the clause "partition by {field2}" would be no use.

Another issue is to provide the values of each columns separately.
Usually, the data should provide row by row like you showed in expected result.


You can find many examples in manuals, in this forum, so on...

Search them with the keyword "OLAP specifications".

How about this:
Using DB2 UDB OLAP functions

Good article with examples.

Additionally, here's an example that uses the SAMPLE database:
Taken from:
Meet the Experts: Bob Lyle Talks About OLAP Functions in DB2 for UNIX, Windows, and OS/2

Also:
From: http://publib.boulder.ibm.com/infoce.../r0023461.html

Enjoy!

Marc