RE: Counting Occurrences with SQL

I think you could do this with OLAP functions (not tested)...

With DayCount (DateVal, DayCount) as
( SELECT date1, ROW_NUMBER() over (order by date1 asc)
FROM (select distinct date1 from myFile ) as f1 )
Select partid, date1, DayCount, date2

From myFile inner join DayCount on (date1=DateVal)

Hth,
-Eric DeLong

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Pete Helgren
Sent: Monday, March 07, 2011 2:41 PM
To: Midrange Systems Technical Discussion
Subject: Counting Occurrences with SQL

I am have a data set that looks like the following:

BB8179 04/17/11 04/02/11
BE7214 04/17/11 04/02/11
BD9607 04/17/11 04/02/11
BB6382 04/17/11 04/02/11
BB7942 04/17/11 04/02/11
BE7487 04/17/11 04/02/11
BE7488 04/18/11 04/02/11
BE2178 04/18/11 04/02/11
BE1618 04/18/11 04/02/11
BE7489 04/18/11 04/02/11
BE2179 04/18/11 04/02/11
BD9608 04/21/11 04/02/11
BE2180 04/21/11 04/02/11
BE8955 04/21/11 04/02/11
BE1696 04/21/11 04/02/11

What I would like to do is count the unique occurrences of the first
date column. Something like:

BB8179 04/17/11 1 04/02/11
BE7214 04/17/11 1 04/02/11
BD9607 04/17/11 1 04/02/11
BB6382 04/17/11 1 04/02/11
BB7942 04/17/11 1 04/02/11
BE7487 04/17/11 1 04/02/11
BE7488 04/18/11 2 04/02/11
BE2178 04/18/11 2 04/02/11
BE1618 04/18/11 2 04/02/11
BE7489 04/18/11 2 04/02/11
BE2179 04/18/11 2 04/02/11
BD9608 04/21/11 3 04/02/11
BE2180 04/21/11 3 04/02/11
BE8955 04/21/11 3 04/02/11
BE1696 04/21/11 3 04/02/11

Actually there are more columns involved here but this data set
illustrates the challenge. Can't seem to wrap my head around the whole
thing but I may need a subselect to get the count of the occurrences...

Thanks