title: Oracle sql search rows containing one or more keyword and sum by its keyword
body: <p>I want to calculate sum by a different text, but the subquery keeps returning multiple rows, instead of a single row.
  I was doing like this:</p>
  
  <pre><code>select sub.aid, 
  sum (case when sub.text like '%abc' then sub.value end),
  sum (case when sub.text like '%def' then sub.value end), ...
  
  from (
     select a.aid, a.value, a.text
     from atable a, btable b
     where a.aid = b.bid and 
         a.aid = any (select aid
                   from atable
                   where text like '%abc' or text like '%def' or text like '%ghi' ....
                   group by aid
                   having count(*) &gt;=1)
  ) sub
  group by sub.aid
  </code></pre>
  
  <p>I am getting ora-01427 single-row subquery returns more than one row error. 
  I don't know what the problem is. How do I get the query work?
  Thanks in advance.</p>