title: How to make an oracle where clause that contains a grouping field?
body: <p>I have a how do I do this in Oracle?</p>
  
  <pre><code>select a.field1 || '_' || 
         b.field2 || '_' || 
         sum(c.field3)
    from table1 a, 
         table2 b, 
         table3 c
   where a.field1 || '_' || 
         b.field2 || '_' || 
         sum(c.field3) not in (select d.field1 || '_' || 
                                      e.field2 || '_' || 
                                      sum(f.field3)  
                                 from table4 d, 
                                      table5 e, 
                                      table6 f
                                where conditional_info_to_join_the_tables 
                                group by d.field1, e.field2)
     and conditional_info_to_join_the_tables  
   group by a.field1, b.field2
  </code></pre>
  
  <p>The error I get is I can't use the sum in the where clause </p>
  
  <p>i have tried using </p>
  
  <pre><code>select a.field1 || '_' || 
         b.field2 || '_' || 
         sum(c.field3), 
         sum(c.field2) foo
    from table1 a, 
         table2 b, 
         table3 c
   where a.field1 || '_' || 
         b.field2 || '_' || 
         foo not in (select d.field1 || '_' || 
                            e.field2 || '_' || 
                            sum(f.field3)  
                       from table4 d, 
                            table5 e, 
                            table6 f
                      where conditional_info_to_join_the_tables 
                      group by d.field1, e.field2)
     and conditional_info_to_join_the_tables 
   group by a.field1, b.field2
  </code></pre>
  
  <p>but that foo was not an identified variable.</p>