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
viv_acious
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
DaveShaw
source
to share
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
Brian webster
source
to share
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
Stv
source
to share