Clever Geek Handbook

SQL - the number of instances in the column

I have a table like this:

and I want to count the occurrences in the "AB" and "Cd" columns in the PageURL column.

ID  User  Activity  PageURL  Date
 1  Me    act1      abcd     2013-01-01
 2  Me    act2      cdab     2013-01-02
 3  You   act2      xyza     2013-02-02
 4  Me    act3      xyab     2013-01-03

I want to have 2 columns ... 1 for counting "ab" and 1 for counting "cd".

In the above example, I get a count of 3 for "ab" and a count of 2 for "cd".

+3


source to share


3 answers


Something like:

select 
   CountAB = sum(case when PageURL like '%ab%' then 1 else 0 end),
   CountCD = sum(case when PageURL like '%cd%' then 1 else 0 end)
from
  MyTable
where
   PageURL like '%ab%' or
   PageURL like '%cd%'


This works if "ab" and "cd" only need to be recalculated once per line. Plus, it's probably not very efficient.

+7


source 


select
  (select count(*) as AB_Count from MyTable where PageURL like '%ab%') as AB_Count,
  (select count(*) as CD_Count from MyTable where PageURL like '%cd%') as CD_Count


+3


source 


SELECT total1, total2 
    FROM (SELECT count(*) as total1 FROM table WHERE PageUrl LIKE '%ab%') tmp1,
         (SELECT count(*) as total2 FROM table WHERE PageUrl LIKE '%cd%') tmp2
0


source






More articles:


All Articles