title: BigQuery: Specify a condition in count()
body: <p>i have a session table which has a userId and a nested record for visited homepage (string) and a timestamp for each visit in one session. Basically a 1:m relationship in one row: </p>
  
  <pre><code>                     UserId|SessionEndTime|page1|timestamp1|
                                            |page2|timestamp2|
                                            |page1|timestamp3|
  </code></pre>
  
  <p>What i want to know is how can i get all userId who have visited my page 6x in the last 6 months and they should have visited pages (with a name like '%astring%') at least 2 times.</p>
  
  <p>This is my approach but how can i bring the like condition into a count()?:</p>
  
  <pre><code>SELECT UserId, COUNT(UserId) AS visitCounter FROM [oxidation.TestVisits]
  WHERE SessionEndTime &gt;= TIMESTAMP_TO_MSEC(DATE_ADD(CURRENT_TIMESTAMP(),-6,"MONTH"))
        AND LastUserId != 'none'
  GROUP BY 1
  HAVING visitCounter&gt;=6
  ORDER BY 1 ASC
  </code></pre>