sql server - SQL - count number of occurrences in a column -

sql server - SQL - count number of occurrences in a column -

i have table follows:

and want count occurrences of "ab" , "cd" in column pageurl.

id user activity pageurl date 1 me act1 abcd 2013-01-01 2 me act2 cdab 2013-01-02 3 act2 xyza 2013-02-02 4 me act3 xyab 2013-01-03

i want have 2 columns...1 count of "ab" , 1 count of "cd".

in above example, count of 3 "ab" , count of 2 "cd".

something like:

select countab = sum(case when pageurl '%ab%' 1 else 0 end), countcd = sum(case when pageurl '%cd%' 1 else 0 end) mytable pageurl '%ab%' or pageurl '%cd%'

this works assuming "ab" , "cd" need counted 1 time per row. also, it's not efficient.

sql sql-server sql-server-2008