Count by Title on specific columns

Question

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

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

Thanks...

what have you tried so far? and when you say "followed by" how are the records sorted? — jtate, Oct 15 '18 at 21:49
To answer your question, we need to know the sort order and the version of SQL Server you are using. Also: What is the difference between "count of intents" and "count of ID's"? What does "have intent" mean? — Wolfgang Kais, Oct 15 '18 at 22:59
Duplicate of [msdn post](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9f034d72-99b2-4a10-ae05-af5d1ef47837/count-by-title-on-specific-columns?forum=transactsql) — SMor, Oct 15 '18 at 23:16
The version I am using is SQL server 17...sorted by date , ID ,intent asc — user3369545, Oct 16 '18 at 00:38
What does "followed by" mean? SQL tables represent *unordered* sets. Your data does not appear to have an explicit ordering. — Gordon Linoff, Oct 16 '18 at 01:17
The top 3 according to your ordering of date,id,intent are not in the correct order. — TomC, Oct 16 '18 at 03:16
Please read & act on [mcve], hits googling 'stackexchange homework' & the downvote arrow mouseover text. Clarify via post edit, not comment. And what is your question? Please do not dump homework/specifications. Show your work & what parts you can do & say how you are stuck. [Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/q/284236/3404097) — philipxy, Oct 16 '18 at 09:11
@ Tom, Gordon ,Wolfgang ..I will going forward make sure that I write the questions with utmost clarity.... — user3369545, Oct 18 '18 at 18:09

score 0 · Answer 1 · answered Oct 15 '18 at 21:50

0

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;

answered Oct 15 '18 at 21:50

Yogesh Sharma

49,870
5
26
52

Thanks Yogesh!..Appreciate your help – user3369545 Oct 16 '18 at 21:11

score 0 · Answer 2 · answered Oct 16 '18 at 06:37

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

score 0 · Accepted Answer · answered Oct 17 '18 at 10:05

    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;

Count by Title on specific columns

3 Answers3