To find the total amount by which the accounts have been debited : The transacted accounts in design of Model-II have been stored in AccCode of VouchersMain and Code of VouchersDetail. The following SQL statement is formed to generate the relevant information from VouchersDetails. SELECT Code, Sum( amount ) AS Total FROM vouchersMain INNER JOIN vouchersDetails ON VouchersMain.Vno = VouchersDetails.Vno WHERE Type = GROUP BY Code ; Similarly, the following SQL statement is formed to generate the required information from VouchersMain table. SELECT AccCode As Code, sum(amount) AS Total FROM vouchersMain INNER JOIN vouchersDetails ON VouchersMain.Vno = VouchersDetails.Vno WHERE Type = GROUP BY AccCode ; Both the SQL statements are meant to extract similar sets of records, but from two different sources.
Therefore, the resultant record set of these SQL statements have been horizontally merged using UNION clause as shown below: SELECT Code, sum(amount) AS Total FROM vouchersMain INNER JOIN vouchersDetails ON VouchersMain.Vno = VouchersDetails.Vno WHERE Type = GROUP BY Code UNION ALL SELECT AccCode As Code, sum(amount) AS Total FROM vouchersMain INNER JOIN vouchersDetails ON VouchersMain.Vno = VouchersDetails.Vno WHERE Type = GROUP BY AcCode ; The above SQL statement is saved as Query101for its subsequent use. The total of debit amount in this query represents the Total with positive amounts. (b) To find the total amount by which the accounts have been credited : In order to ascertain the total amount by which every transacted account has been credited, a query similar to that in (a) need be formed. This is achieved by substituting Debit field in SELECT and GROUP BY clause by Credit field and the sum of amount generated by sum(Amount) is multiplied by- so that the final amount assigned to Total field is always negative.
Accordingly, the following SQL statement is formed : SELECT Code, sum(amount)*- AS Total FROM vouchersMain INNER JOIN vouchersDetails ON VouchersMain.Vno=VouchersDetails.Vno WHERE Type= GROUP BY Code, Amount UNION SELECT AccCode As Code, sum(amount)*- AS Total FROM vouchersMain INNER JOIN vouchersDetails ON VouchersMain.Vno=VouchersDetails.Vno WHERE Type= GROUP BY AccCode, Amount; I n the above SQL statement, the sum of