Find number of occurences of event per day – SQL

I’m trying to set up some monitoring in my SQL database.

select
        gn.Goal_Name_
        ,gn.EventTimestamp as   Timestamp
        --,Max(EventTimestamp) as   Timestamp
        from(
            select CASE when substr(GoalName,1,3)='MSD' then 'MSD' when substr(GoalName,1,5)='https' then 'https' else goalname END as Goal_Name_ 
                    
                    ,EventTimestamp
                from    CG.Goal as goal
        )gn
        group by 1,2      

Produces a table with a structure like:

The problem i’m having is finding a way to count each distinct Goal Name for each day. Find the daily occurence really.

>Solution :

You are almost there. The only thing missing is converting your timestamp to date and count the number of rows.

select
     gn.Goal_Name_
     ,CAST(gn.EventTimestamp AS DATE FORMAT 'YYYY/MM/DD') as eventDay
     ,Count(*) as   GoalsCount
     from(
          select CASE when substr(GoalName,1,3)='MSD' then 'MSD' when 
          substr(GoalName,1,5)='https' then 'https' else goalname END as Goal_Name_ 
          ,EventTimestamp
          from CG.Goal as goal
     )gn
group by 1,2