title: SQL query to select a column with expression of non-aggregate value and aggregate function
body: <p>Tables used: </p>
  
  <blockquote>
    <p>1) v(date d, name c(25), desc c(50), debit n(7), credit n(7))</p>
    
    <p>name in 'v' refers name in vn table</p>
    
    <p>2) vn(date d, name c(25), type c(25), obal n(7))</p>
    
    <p>name in 'vn' is a primary key and different names are grouped by type</p>
    
    <p>ex: names abc, def, ghi belongs to type 'bank', names xyz, pqr belongs to type 'ledger', ...</p>
  </blockquote>
  
  <p>I've a query like this: </p>
  
  <pre><code>SELECT vn.type, SUM(vn.obal + IIF(v.date &lt; sd, v.credit-v.debit, 0)) OpBal, ;
      SUM(IIF(BETWEEN(v.date, sd, ed), v.credit-v.debit, 0)) CurBal ;
      FROM v, vn WHERE v.name = vn.name GROUP BY vn.type ;
      ORDER BY vn.type HAVING OpBal + CurBal != 0
  </code></pre>
  
  <p>It works fine but the only problem is, obal is a value which is entered only once per name in table 'vn' but with this query for every calculation of credit-debit in table 'v', obal is added multiple times and displayed under OpBal. When the query is modified like below:</p>
  
  <pre><code>SELECT vn.type, vn.obal + SUM(IIF(v.date &lt; sd, v.credit-v.debit, 0)) OpBal, ;
      SUM(IIF(BETWEEN(v.date, sd, ed), v.credit-v.debit, 0)) CurBal ;
      FROM v, vn WHERE v.name = vn.name GROUP BY vn.type ;
      ORDER BY vn.type HAVING OpBal + CurBal != 0
  </code></pre>
  
  <p>it shows an error message like 'Group by clause is missing or invalid'!</p>
  
  <p>RDBMS used MS Visual Foxpro 9. sd and ed are date type variables used for the purpose of query where sd &lt; ed. </p>
  
  <p>Please help me out getting the expected result. Thanks a lot.</p>