Geek asks and answers

SQL release group

Here is the problem. Here is 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 would like to group by campaign, but only as long as it is the same. The result that I expect is as follows:

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 a campaign, I get only one row 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 how I can get the desired result?

Thank!

+3
source share
4 answers

There is no way in the ANSI SQL standard statement to do what you are looking for. what was said above about the stored procedure or the use of cursors can be done, but you can also write instructions in T-SQL or PL / SQL using cursors to determine the amount of each of the origin and time of the start of the reference - 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

, , , , Microsoft Box

+4

, SQL . , SQL-. , , . sproc .

+2

, , , :

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

, , , "" , , , . , , .

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 can easily just use a group, for example

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

What will 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
0
source

More articles:


All Articles