Re: How to count how many times a field value changes ?

On 16-Apr-98 18:09:11 xavier.grosjean wrote:

>Hello

>I'm quite new to sql, plsql and Pro-C. Do not laught, I DID NOT ask for
>it ! My problem is: How to count how many times one column value
>changes, in relation to one other column value Ex: How many times does
>col2 change of values here : col1 col2
>111 221
>111 222 change 1
>111 221 change 2
>111 222 change 3
>112 221 change 4
>112 221
>112 222 change 5
>112 222
>112 222
>113 221 not a change: col1 is not the same !
>113 221
>113 222 change 6
>113 221 change 7
>113 223 change 8

>I think it might not be done by one single request, and probably needs
>a pls/sql program, with a cursor. But I thought I would ask, just in
>case. So please, let me know ! Regards and thanks. Xavier

Well, the problem is that a SQL-Statement gives you a *set* of data with no implicit order. That means if you insert the above data into a table, say your_table, and then do something like

select * from your_table;

you may or may not get the data in the above order. Okay, you may use an additional column with an unique ID number.

Assuming you get the data in the desired order, you could try something like

select a.col1,count(a.col2)
from

   (select rownum row,col1,col2 from your_table) a,    (select rownum row,col1,col2 from your_table) b where

   a.row+1=b.row and
   a.col1=b.col1 and
   a.col2<>b.clo2;

You eventually have to replace the rownum above by a unique ID column which must be without gaps or 'a.id+1=b.id' will not work. The best way is probably using rownum and ordering by a separate ID column.

Another thing you could do is to get the number of distinct values of col2 for each value of col1 using the following:

select col1,count(distinct col2)
from your_table
group by col1;

Beware that you might have to adjust the syntax slightly as I'm writing this at home and have neither oravcle nor docs to check.

Hope that helps,
Lothar

--

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Schulstr. 12            | lothar.armbruester_at_t-online.de
D-65375 Oestrich-Winkel |