SQL group by issue

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              开发者_如何学C 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!

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

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.

You seem to want to group by date, then Campaign, like so:

SELECT DateTime, Campaign, Count(Campaign) as Count 
FROM myTable 
GROUP BY DateTime, Campaign

I know this isn't what you asked for but if the "Campaign" column isn't unique I would add another table that had a unique identifier for each Campaign for example an auto incremented data type. This way it wouldn't matter if you had two campaigns with the same name.

FirstTable

UniqueCampaign      CampaignName
1                   Campaign1
2                   Campaign2
3                   Campaign1

SecondTable

DateTime               UniqueCampaign      
2011-06-01 08:55:00    1
2011-06-01 08:56:00    1
2011-06-01 08:57:00    2
2011-06-01 08:58:00    3
2011-06-01 08:59:00    3

Then you could easily just use a group by like

SELECT MIN(DateTime) [DateTime], T1.UniqueCampaign, COUNT(*) CampaignCount
FROM SecondTable T1
INNER JOIN FirstTable T2
     ON T1.UniqueCampaign = T2.UniqueCampaign
Group By T1.UniqueCampaign

Which would give you

DateTime                    UniqueCampaign  CampaignCount
2011-06-01 08:55:00.000     1               2
2011-06-01 08:57:00.000     2               1
2011-06-01 08:58:00.000     3               2