Re: Counting Occurrences with SQL

[Pete Helgren <pete@xxxxxxxxxx>]

Absolutely brilliant! Worked perfectly.

Thanks VERY much.

Pete Helgren
Value Added Software, Inc
www.asaap.com
www.opensource4i.com


On 3/7/2011 2:50 PM, Schutte, Michael D wrote:
> Refined one more time. Dense_rank will work the way you want without using any cte, partition by or group by,
>
> Select column1, column2,
> dense_rank() over(order by column2), column3 from qtemp/test
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Schutte, Michael D
> Sent: Monday, March 07, 2011 4:28 PM
> To: Midrange Systems Technical Discussion
> Subject: RE: Counting Occurrences with SQL
>
> Actually you are going to want  dense_rank
>
>
> Select column1, column2,
> dense_rank() over(partition by column3 order by
> column2), column3 from qtemp/test
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Pete Helgren
> Sent: Monday, March 07, 2011 3: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
>
> --
> Pete Helgren
> Value Added Software, Inc
> www.asaap.com
> www.opensource4i.com
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
> ________________________________
>
> Notice from Bob Evans Farms, Inc: This e-mail message, including any attachments, may contain confidential information that is intended only for the person or entity to which it is addressed. Any unauthorized review, use, disclosure or distribution is strictly prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message and any attachments.