SQL query to select a column with expression of non-aggregate value and aggregate function

Question

Tables used:

1) v(date d, name c(25), desc c(50), debit n(7), credit n(7))

name in 'v' refers name in vn table

2) vn(date d, name c(25), type c(25), obal n(7))

name in 'vn' is a primary key and different names are grouped by type

ex: names abc, def, ghi belongs to type 'bank', names xyz, pqr belongs to type 'ledger', ...

I've a query like this:

SELECT vn.type, SUM(vn.obal + IIF(v.date < 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

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:

SELECT vn.type, vn.obal + SUM(IIF(v.date < 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

it shows an error message like 'Group by clause is missing or invalid'!

RDBMS used MS Visual Foxpro 9. sd and ed are date type variables used for the purpose of query where sd < ed.

Please help me out getting the expected result. Thanks a lot.

Answer 1

I saw the SQL Syntax for SQL with VFP for the first time a few minutes ago, so this could well be full of errors, but as a 'guessful hunch':

SELECT vn.type, 
       SUM(vn.obal + (SELECT SUM(IIF(v.date < sd, v.credit-v.debit, 0)) 
                      FROM v 
                      WHERE v.name = vn.name)) OpBal,
       SUM(SELECT SUM(IIF(BETWEEN(v.date, sd, ed), v.credit-v.debit, 0))
           FROM v 
           WHERE v.name = vn.name) CurBal
FROM vn
GROUP BY vn.type
ORDER BY vn.type 
HAVING OpBal + CurBal != 0

Basically, I've just turned selection from v into subselects to avoid vn.obal to be repeated. It shouldn't matter for v that it first gets the sum for the individual person before summing them all together.