SQL group by issue

Question

Here's an issue I have. Here's an example:

DateTime               Campaign
2011-06-01 08:55:00    Campaign1
2011-06-01 08:56:00    Campaign1
2011-06-01 08:57:00    Campaign2
2011-06-01 08:58:00    Campaign1
2011-06-01 08:59:00    Campaign1

I'd like to group by campaign, but only as long as it is the same one. The the result I expect is:

DateTime               Campaign    Count
2011-06-01 08:55:00    Campaign1       2
2011-06-01 08:57:00    Campaign2       1
2011-06-01 08:58:00    Campaign1       2

Whenever I group on campaign, I get only one line with Campaign1

Select *, Count(Campaign) as Count from myTable group by Campaign

Results in

DateTime               Campaign    Count
2011-06-01 08:55:00    Campaign1       4
2011-06-01 08:57:00    Campaign2       1

Do you have any idea of how I could get the desired result?

Thank you!

Answer 1

There is not a way of doing what you are looking for in a standard ANSI SQL statement. what was stated above about an Stored Procedure or using cursors can be done - but you can also write the statements into a T-SQL or PL/SQL using cursors to determine the number of each from beginning start time and count - something like this in T-SQL (Microsoft SQL server):

USE [<database name here>]
DECLARE @tbl_result TABLE (Result_DT DATETIME, Campaign VARCHAR(25), unit_count int);
DECLARE @start_dt DATETIME,
        @current_campaign VARCHAR(25), --This can be any length needed - if your campaigns are over 25 chars long
        @record_dt DATETIME,
        @campaign VARCHAR(25),
        @cur_records CURSOR,
        @Record_Count INT;
SET @current_campaign = 'Start'
SET @cur_records = CURSOR
FOR SELECT DateTime, Campaign FROM myTable order by 1,2

OPEN @cur_records

FETCH NEXT FROM @cur_records INTO @record_dt, @campaign
WHILE @@FETCH_STATUS = 0
    BEGIN
       -- CHECK THE record to the Current Campaign for grouping
        IF @campaign <> @current_campaign
            BEGIN
              If @current_campaign <> 'Start'
                BEGIN
                    INSERT INTO @tbl_result 
                    VALUES (@start_dt, @current_campaign, @Record_Count)
                END
            SET @current_campaign = @campaign
            SET @start_dt = @record_dt
            SET @Record_Count = 1
            END
        ELSE
            BEGIN
                SET @Record_Count = @Record_Count + 1
            END
    END
    INSERT INTO @tbl_result  -- Inserting the last of the Records
    VALUES (@start_dt, @current_campaign, @Record_Count)
-- Now to display the results
SELECT Result_DT, Campaign, unit_count FROM @tbl_result order by 1, 2

Now forgive me if this errors out - but it should be the basic structure of what you need if it is a Microsoft Box

Answer 2

This is a problem that's pretty hard to solve in SQL and really easy to solve in other ways. I don't even think this can be done in a single raw SQL query. The problem is that the group by isn't an aggregate of the properties of the individual columns, but is dependent on other columns as well. You'd be better off writing a sproc with a cursor or doing it programmatically elsewhere.