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.

CodePudding user response：

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