sql - Select Query for Days of the Week -

when using following query return days of week i'd show specific day (monday, example) , count number of times 'monday' returned.

     select       w.[id]     , w.[name]     , [status]     , [initiatorpersonaliasid]     , p.[firstname]     , p.[lastname]     , ( select top 1 [value]         [attributevalue] av         inner join [attribute] on a.[id] = av.[attributeid]          , a.[entitytypeid] = 113          , a.[entitytypequalifiercolumn] = 'workflowtypeid'          , a.[entitytypequalifiervalue] = w.[workflowtypeid]         [entityid] = w.[id]          , a.[key] = 'dayoftheweek'       ) [day]     , (select top 1 [value]         [attributevalue] av         inner join [attribute] on a.[id] = av.[attributeid]          , a.[entitytypeid] = 113          , a.[entitytypequalifiercolumn] = 'workflowtypeid'          , a.[entitytypequalifiervalue] = w.[workflowtypeid]         [entityid] = w.[id]          , a.[key] = 'time'       ) [time] [workflow] w inner join [personalias] pa on pa.id = w.[initiatorpersonaliasid] inner join [person] p on p.[id] = pa.[personid]  w.[workflowtypeid] = 1032 

any appreciated.

thanks!

you can write as:

;with cte_daycount (         select av.[value] daycount,w.[id]  id         [attributevalue] av         inner join [workflow] w on av.[entityid] = w.[id]          inner join [attribute] on a.[id] = av.[attributeid]                  , a.[entitytypeid] = 113          , a.[entitytypequalifiercolumn] = 'workflowtypeid'          , a.[entitytypequalifiervalue] = w.[workflowtypeid]         , a.[key] = 'dayoftheweek'         [value] '%monday%' -- add day value here         group w.[id], av.value  )                 select count(dc.daycount) total  [workflow] w inner join [personalias] pa on pa.id = w.[initiatorpersonaliasid] inner join [person] p on p.[id] = pa.[personid]  inner join cte_daycount dc on dc.id = w.[id] w.[workflowtypeid] = 1032