How to have count text like in SQL?

Question

How to have count text like in SQL ?

IF OBJECT_ID('tempdb..#t') IS NOT NULL
    DROP TABLE #t

Create table #t (message nvarchar(4000), messagedate datetime)

insert into #t values ('Column Listing have data error', getdate())
WAITFOR DELAY '00:00:00:02';
insert into #t values ('Column Listing have name error', getdate())
WAITFOR DELAY '00:00:00:02';
insert into #t values ('Column Listing have city error', getdate())

select * from #t

select count(message like 'column%') , max(messagedate) from #t
group by message

Answer 1

With conditional aggregation but I don't see where you should use grouping:

select 
  sum(case when message like 'column%' then 1 else 0 end) counter, 
  max(messagedate) maxdate
from #t

If you also want the max date only for the messages that are like 'column%':

select 
  count(*) counter, 
  max(messagedate) maxdate
from #t
where message like 'column%'

See the demo.
Results:

> counter | maxdate            
> ------: | :------------------
>       3 | 16/04/2019 16:50:25

Answer 2

Throw the logic part into a subquery, then just count in the main query..

SELECT Message, COUNT(Message) as MessageCount, MAX(MessageDate) as MaxMessageDate
FROM (SELECT Message, messagedate FROM #t WHERE Message LIKE 'column%') a
GROUP BY Message