按特定列的标题计算

Date       ID       intent 
9/1/2018    1   fetch it 
9/1/2018    1   Ask it
9/1/2018    1   default
9/1/2018    2   remand
9/1/2018    2   choose it
9/2/2018    1   fetch it 
9/2/2018    1   choose 
9/2/2018    1   default
9/3/2018    4   Ask it
9/3/2018    4   fetch it 
9/4/2018    5   hello
9/4/2018    1   call
9/4/2018    1   default
9/5/2018    1   remand
9/5/2018    1   default
9/5/2018    2   fetch it 
9/5/2018    2   default

For the above table : I am trying to get the following output...Can you please help here...

对于上表:我正在尝试获得以下输出...请你帮忙...

count of intents that have intent followed by default on a day to day basis

每天默认具有意图的意图计数

9/1/2018    1
9/2/2018    1
9/3/2018    0
9/4/2018    1
9/5/2018    2

Name of intents that have intent followed by default on a daily basis

每天默认具有意图的意图的名称

9/1/2018    ask it
9/2/2018    choose
9/3/2018    
9/4/2018    call
9/5/2018    remand 
9/5/2018    fetch it

Count of ID's that have intent followed by default on a day to day basis

每天默认具有意图的ID的计数

9/1/2018    1
9/2/2018    1
9/3/2018    0
9/4/2018    1
9/5/2018    2

Thanks...

3 个解决方案

#1

    DECLARE @Sample TABLE
    (
        theDate DATE NOT NULL,
        ID INT NOT NULL,
        Intent VARCHAR(20) NOT NULL
    );

INSERT  @Sample
VALUES  ('20180901', 1, 'fetch it'),
    ('20180901', 1, 'Ask it'),
    ('20180901', 1, 'default'),
    ('20180901', 2, 'remand'),
    ('20180901', 2, 'choose it'),
    ('20180902', 1, 'fetch it'),
    ('20180902', 1, 'choose'),
    ('20180902', 1, 'default'),
    ('20180903', 4, 'Ask it'),
    ('20180903', 4, 'fetch it'),
    ('20180904', 5, 'hello'),
    ('20180904', 1, 'call'),
    ('20180904', 1, 'default'),
    ('20180905', 1, 'remand'),
    ('20180905', 1, 'default'),
    ('20180905', 2, 'fetch it'),
    ('20180905', 2, 'default');

-- swePeso
WITH cteData(theDate, Task1, Task2, Task3)
AS (
    SELECT      theDate,
            SUM(CASE WHEN Intent = 'default' THEN 1 ELSE 0 END) AS Task1,
            MIN(CASE WHEN Intent = 'default' THEN NULL ELSE Intent END) AS Task2,
            MAX(CASE WHEN Intent = 'default' THEN ID ELSE NULL END) AS Task3
    FROM        @Sample
    GROUP BY    theDate,
            ID
)
SELECT      theDate,
        SUM(Task1) AS Task1,
        MAX(CASE WHEN Task1 = 1 THEN Task2 ELSE '' END) AS Task2,
        COUNT(DISTINCT Task3) AS Task3
FROM        cteData
GROUP BY    theDate
ORDER BY    theDate;

#2

I would go with first sample output :

我会选择第一个示例输出:

select Date, sum(case when intent = 'default' then 1 else 0 end) 
from table t
group by Date;       

#3

is this helpful.?

这有用吗?

    Create Table #tmp([Date] Date,ID int, intent Varchar(10))

    Insert into #tmp
    SELECT '9/1/2018',1,'fetch it' Union All
    SELECT '9/1/2018',1,'Ask it' Union All
    SELECT '9/1/2018',1,'default' Union All
    SELECT '9/1/2018',2,'remand' Union All
    SELECT '9/1/2018',2,'choose it' Union All
    SELECT '9/2/2018',1,'fetch it' Union All
    SELECT '9/2/2018',1,'choose' Union All
    SELECT '9/2/2018',1,'default' Union All
    SELECT '9/3/2018',4,'Ask it' Union All
    SELECT '9/3/2018',4,'fetch it' Union All
    SELECT '9/4/2018',5,'hello' Union All
    SELECT '9/4/2018',1,'call' Union All
    SELECT '9/4/2018',1,'default' Union All
    SELECT '9/5/2018',1,'remand'Union All
    SELECT '9/5/2018',1,'default' Union All
    SELECT '9/5/2018',2,'fetch it' Union All
    SELECT '9/5/2018',2,'default' 


    ;with cte
    As
    (
        Select *,LEAD(intent) over(partition by [Date] order by (Select null)) as FollowingIntent
        from #tmp   
    )

    Select Date, SUM(CASE WHEN FollowingIntent='default' then 1 else 0 end)
    from cte
    Group by [Date]

    Drop Table #tmp