Group repeated rows in TSQL

Question

I have the following table and data in SQL Server 2005:

create table LogEntries (
  ID int identity,
  LogEntry varchar(100)
)

insert into LogEntries values ('beans')
insert into LogEntries values ('beans')
insert into LogEntries values ('beans')
insert into LogEntries values ('cabbage')
insert into LogEntries values ('cabbage')
insert into LogEntries values ('beans')
insert into LogEntries values ('beans')

I would like to group repeated LogEntries so that I have the following results:

LogEntry  EntryCount
beans     3
cabbage   2
beans     2

Can you think of any way to do this in TSQL outside of using a cursor?

Answer 1

SQL not exactly my strong point but won't

SELECT LogEntry, COUNT(1) AS Counter FROM LogEntries GROUP BY LogEntry

do it?

Answer 2

I don't think that you can do this with one query. In order to provide counts in a query, you need to group using the LogEntry column. However, this will just give you total counts per LogEntry, and not the counts of the numbers of entries in sequence that you are looking for. I think that a cursor is called for (or bring the whole dataset to your application, and use logic there to get the results that you want).

Answer 3

unless my brain has yet to boot up this morning

SELECT LogEntry, COUNT(LogEntry) as EntryCount FROM LogEntries GROUP BY LogEntry

Answer 4

Now I have looked at the eactual question closely enough :-)

Hmm, on reconsidering, why not just use a cursor? The performance isn't always worse than straight SQL - and it'd certianly be easy for other people to follow the code when they come to look at it. Wrap it in a stored proc or function and you'd be able to use it pretty much anywhere you might need.

Answer 5

This is a set-based solution for the problem. The performance will probably suck, but it works :)

CREATE TABLE #LogEntries (
  ID INT IDENTITY,
  LogEntry VARCHAR(100)
)

INSERT INTO #LogEntries VALUES ('beans')
INSERT INTO #LogEntries VALUES ('beans')
INSERT INTO #LogEntries VALUES ('beans')
INSERT INTO #LogEntries VALUES ('cabbage')
INSERT INTO #LogEntries VALUES ('cabbage')
INSERT INTO #LogEntries VALUES ('carrots')
INSERT INTO #LogEntries VALUES ('beans')
INSERT INTO #LogEntries VALUES ('beans')
INSERT INTO #LogEntries VALUES ('carrots')

SELECT logentry, COUNT(*) FROM (
    SELECT logentry, 
    ISNULL((SELECT MAX(id) FROM #logentries l2 WHERE l1.logentry<>l2.logentry AND l2.id < l1.id), 0) AS id
    FROM #LogEntries l1
) AS a
GROUP BY logentry, id


DROP TABLE #logentries

Results:

beans   3
cabbage 2
carrots 1
beans   2
carrots 1

The ISNULL() is required for the first set of beans.

Answer 6

I think this will do it... didn't check too thoroughly though

select 
    COUNT(*),subq.LogEntry 
from 
(
    select 
     ROW_NUMBER() OVER(ORDER BY id)-ROW_NUMBER() OVER(PARTITION BY logentry ORDER BY id) as t,*
    from 
     LogEntries
) subq 
group by 
    subq.t,subq.LogEntry 
order by 
    MIN(subq.ID)