none
how to return number of record per match criteria RRS feed

  • 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

    Tuesday, September 10, 2019 11:25 AM

Answers

  • 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]


    Tuesday, September 10, 2019 11:38 AM

All replies


  • 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)

    Tuesday, September 10, 2019 11:34 AM
  • 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]


    Tuesday, September 10, 2019 11:38 AM