counting records only when value is different than previous record (MSSQL 2000)

counting records only when value is different than previous record (MSSQL 2000)

Post by Curtis Gaylor » Sat, 25 May 2002 22:08:21



Any tips on this (using MSSQL 2000)

Table_ID   Field1  Field2
1                4            5
6                4            3
3                5            8
2                6            8
4                6            8
5                6            2
7                7            1
9                9            8
10              9            8
12              9            2
11              9            2

The recordset is sorted by Field1.  Then we only want to count a row when
Field2 is not equal to the preceding record.  In this case, we would get:

Field2   CountOfField2
1            1
2            2
3            1
5            1
8            2

 
 
 

counting records only when value is different than previous record (MSSQL 2000)

Post by Curtis Gaylor » Sun, 26 May 2002 04:06:16


This appears to work:

SELECT     Column2, COUNT(*) AS CountOfCol2
FROM         myTable
WHERE     (NOT EXISTS
                          (SELECT     TOP 1 table_id
                            FROM          myTable AS z
                            WHERE      ((z.table_id = (myTable.table_id -
1)) AND (table_id.column2 = z.column2))
                            ORDER BY column2, table_id))
GROUP BY column2
ORDER BY column2


Quote:> Any tips on this (using MSSQL 2000)

> Table_ID   Field1  Field2
> 1                4            5
> 6                4            3
> 3                5            8
> 2                6            8
> 4                6            8
> 5                6            2
> 7                7            1
> 9                9            8
> 10              9            8
> 12              9            2
> 11              9            2

> The recordset is sorted by Field1.  Then we only want to count a row when
> Field2 is not equal to the preceding record.  In this case, we would get:

> Field2   CountOfField2
> 1            1
> 2            2
> 3            1
> 5            1
> 8            2


 
 
 

counting records only when value is different than previous record (MSSQL 2000)

Post by Thomas R. Humme » Sun, 26 May 2002 09:33:28


I'm not positive that I understand this problem correctly, but if you
are only ordering by Field1 when considering this, then you cannot get a
definite resultset or answer. There is no guarantee that the records
will sort in the same way for those records where Field1 is equal. For
example, for Table_IDs 2, 4 and 5, Field1 is equal to 6. That means that
they could come out as:

Table_ID   Field1  Field2
2          6       8
4          6       8
5          6       2

OR

Table_ID   Field1  Field2
5          6       2
2          6       8
4          6       8

This would change the number of records with Field2 = 8 where the
preceding record Field2 <> 8.

Does that make sense? Of course, if you're sorting by both Field1 and
Table_ID, then this is not a problem.

        HTH,

Thomas R. Hummel
Application Wizards, Inc.



Quote:> Any tips on this (using MSSQL 2000)

> Table_ID   Field1  Field2
> 1                4            5
> 6                4            3
> 3                5            8
> 2                6            8
> 4                6            8
> 5                6            2
> 7                7            1
> 9                9            8
> 10              9            8
> 12              9            2
> 11              9            2

> The recordset is sorted by Field1.  Then we only want to count a row when
> Field2 is not equal to the preceding record.  In this case, we would get:

> Field2   CountOfField2
> 1            1
> 2            2
> 3            1
> 5            1
> 8            2

 
 
 

counting records only when value is different than previous record (MSSQL 2000)

Post by Curtis Gaylor » Sun, 26 May 2002 20:51:41


Thanks for the response.  Yes, we are sorting by Field1, Table_ID (not just
Field1).  Also, the solution I posted was incorrect.  In changing the names
to generic i missed typed the sort order for the in-line select.  Here it is
corrected (i also changed the word Field to Column).

SELECT     Column2, COUNT(*) AS CountOfCol2
FROM         myTable
WHERE     (NOT EXISTS
                          (SELECT     TOP 1 table_id
                            FROM          myTable AS z
                            WHERE      ((z.table_id = (myTable.table_id -
1)) AND (table_id.column2 = z.column2))
                            ORDER BY column1, table_id))
GROUP BY column2
ORDER BY column2



> I'm not positive that I understand this problem correctly, but if you
> are only ordering by Field1 when considering this, then you cannot get a
> definite resultset or answer. There is no guarantee that the records
> will sort in the same way for those records where Field1 is equal. For
> example, for Table_IDs 2, 4 and 5, Field1 is equal to 6. That means that
> they could come out as:

> Table_ID   Field1  Field2
> 2          6       8
> 4          6       8
> 5          6       2

> OR

> Table_ID   Field1  Field2
> 5          6       2
> 2          6       8
> 4          6       8

> This would change the number of records with Field2 = 8 where the
> preceding record Field2 <> 8.

> Does that make sense? Of course, if you're sorting by both Field1 and
> Table_ID, then this is not a problem.

> HTH,

> Thomas R. Hummel
> Application Wizards, Inc.



> > Any tips on this (using MSSQL 2000)

> > Table_ID   Field1  Field2
> > 1                4            5
> > 6                4            3
> > 3                5            8
> > 2                6            8
> > 4                6            8
> > 5                6            2
> > 7                7            1
> > 9                9            8
> > 10              9            8
> > 12              9            2
> > 11              9            2

> > The recordset is sorted by Field1.  Then we only want to count a row
when
> > Field2 is not equal to the preceding record.  In this case, we would
get:

> > Field2   CountOfField2
> > 1            1
> > 2            2
> > 3            1
> > 5            1
> > 8            2

 
 
 

counting records only when value is different than previous record (MSSQL 2000)

Post by Curtis Gaylor » Mon, 27 May 2002 22:52:33


Tom

The following was posted by
in the microsoft.public.sqlserver.programming newsgroup and appears to
resolve a number of the issues with our solution (including the one's you
pointed out).

create table Curtis (
  Table_ID int,
  Column1 int,
  Column2 int
)
insert into Curtis values (1,4,5)
insert into Curtis values (6,4,1)
insert into Curtis values (3,5,7)
insert into Curtis values (2,6,8)
insert into Curtis values (4,5,8)
insert into Curtis values (5,6,2)
insert into Curtis values (7,7,1)
insert into Curtis values (9,9,8)
insert into Curtis values (10,9,8)
insert into Curtis values (13,9,2)
insert into Curtis values (12,8,3)
insert into Curtis values (11,9,2)

select *, case when
Column2 <> isnull((
  select top 1 Column2
  from Curtis C1
  where C1.Column1 < C2.Column1
  or (C1.Column1 = C2.Column1 and C1.Table_id < C2.Table_id)
  order by Column1 desc, Table_ID desc
),-1) then '*'+right(Column2,3) else ' '+right(Column2,3) end as Counted
from Curtis C2
order by Column1, Table_ID

go

--Calculate the counts
select
  Column2,
  count(*) as SteveCount
from Curtis C2
where Column2 <> isnull((
  select top 1 Column2
  from Curtis C1
  where C1.Column1 < C2.Column1
  or (C1.Column1 = C2.Column1 and C1.Table_id < C2.Table_id)
  order by Column1 desc, Table_ID desc
),-1)
group by Column2
order by Column2

go

drop table Curtis



> I'm not positive that I understand this problem correctly, but if you
> are only ordering by Field1 when considering this, then you cannot get a
> definite resultset or answer. There is no guarantee that the records
> will sort in the same way for those records where Field1 is equal. For
> example, for Table_IDs 2, 4 and 5, Field1 is equal to 6. That means that
> they could come out as:

> Table_ID   Field1  Field2
> 2          6       8
> 4          6       8
> 5          6       2

> OR

> Table_ID   Field1  Field2
> 5          6       2
> 2          6       8
> 4          6       8

> This would change the number of records with Field2 = 8 where the
> preceding record Field2 <> 8.

> Does that make sense? Of course, if you're sorting by both Field1 and
> Table_ID, then this is not a problem.

> HTH,

> Thomas R. Hummel
> Application Wizards, Inc.



> > Any tips on this (using MSSQL 2000)

> > Table_ID   Field1  Field2
> > 1                4            5
> > 6                4            3
> > 3                5            8
> > 2                6            8
> > 4                6            8
> > 5                6            2
> > 7                7            1
> > 9                9            8
> > 10              9            8
> > 12              9            2
> > 11              9            2

> > The recordset is sorted by Field1.  Then we only want to count a row
when
> > Field2 is not equal to the preceding record.  In this case, we would
get:

> > Field2   CountOfField2
> > 1            1
> > 2            2
> > 3            1
> > 5            1
> > 8            2

 
 
 

counting records only when value is different than previous record (MSSQL 2000)

Post by Curtis Gaylor » Tue, 28 May 2002 03:40:18


If this of interest, I'm moving all posts to
microsoft.public.sqlserver.programming.  More happening there.


Quote:> Any tips on this (using MSSQL 2000)

> Table_ID   Field1  Field2
> 1                4            5
> 6                4            3
> 3                5            8
> 2                6            8
> 4                6            8
> 5                6            2
> 7                7            1
> 9                9            8
> 10              9            8
> 12              9            2
> 11              9            2

> The recordset is sorted by Field1.  Then we only want to count a row when
> Field2 is not equal to the preceding record.  In this case, we would get:

> Field2   CountOfField2
> 1            1
> 2            2
> 3            1
> 5            1
> 8            2

 
 
 

counting records only when value is different than previous record (MSSQL 2000)

Post by Curtis Gaylor » Tue, 28 May 2002 03:41:51



 
 
 

1. counting record only when field value is not equal to value of previous record

Any tips on this (using MSSQL 2000)

Table_ID   Field1  Field2
1                4            5
6                4            3
3                5            8
2                6            8
4                6            8
5                6            2
7                7            1
9                9            8
10              9            8
12              9            2
11              9            2

The recordset is sorted by Field1.  Then we only want to count a row when
Field2 is not equal to the preceding record.  In this case, we would get:

Field2   CountOfField2
1            1
2            2
3            1
5            1
8            2

2. SQLXMLRequest object support in SQLServer 2000

3. Increment Number for next record from previous record value

4. Null parameters in a Java Store procedure

5. Update a record using a previous records value

6. Sybase ASA vs MSDE performance comparison

7. copying values from previous record into new record.....................

8. Database integrity

9. SQLServer 2000: Returning parent records only when matching all supplied child record values

10. getting previous record data in access 2000

11. Previous and Next record surrounding one specific record

12. Previous record and next record in pl/sql

13. UPDATE Using Previous Record Value