how to return number of record per match criteria

Question

Dear all,

I have a table where I need to extract records with match 3 type of criteria as define below :

SELECT * FROM my TABLE WHERE
 myField LIKE '%a%'
OR myField LIKE '%b%'
OR myField LIKE '%c%' 

What I need to return is also the number of records which match each of those LIKE operator.

Any idea how can I perform this '

regards

Answer 1

What I need to return is also the number of records which match each of those LIKE operator.

Any idea how can I perform this '

regards

You mean, you need 3 counts for those 3 conditions? 
If possible, please show us how the query result should look like.

---

Cheers
Vaibhav
MCSA (SQL Server 2014)

Answer 2

Try it with conditional summing:

SELECT count(*) AS TotalCnt,
       sum(case when  myField LIKE '%a%' then 1 else 0 end) AS Acnt,
       sum(case when  myField LIKE '%b%' then 1 else 0 end) AS Bcnt,
       sum(case when  myField LIKE '%c%' then 1 else 0 end) AS Ccnt
FROM myTABLE 
WHERE  myField LIKE '%a%'
OR myField LIKE '%b%'
OR myField LIKE '%c%' 

---

Olaf Helper

[ Blog] [ Xing] [ MVP]