title: sum() function in sql doubles the amount when used with multiple table
body: <p>i have been banging my head for the solution for nearly 14 hours and still not able to find out a solution. I will try and make my problem as clear as i can. Please help me out</p>
  <p>i have 2 tables</p>
  <ol>
  <li>cash</li>
  <li>mas</li>
  </ol>
  <p>the structure of the tables is like</p>
  <h2>mas table</h2>
  <p>id - int</p>
  <p>dateofissue - datetime</p>
  <p>amount - float</p>
  <hr />
  <h2>cash table</h2>
  <p>id - int</p>
  <p>date - datetime</p>
  <p>amount - float</p>
  <hr />
  <p>now what i want is to make a monthly report which tells the date wise sum total of the amounts of cash and from the master table</p>
  <p>the structure of the report will be like</p>
  <hr />
  <p>date------------------ master amount --------------- cash amount</p>
  <p>2/3/2012--------------  2000 ------------------------ 1000</p>
  <p>3/3/2102--------------  4000 ------------------------ 200</p>
  <p>...</p>
  <p>....</p>
  <hr />
  <p>the total of amount from the mas table and the total of the amount from the cash table is grouped according to the dateofissue from mas table and date from cash table.</p>
  <p>i am using a sql query like</p>
  <pre><code>     SELECT     sum(cash.amount) as CashAmount,  sum ( mas.amount) AS DraftAmount, mas.dateofissue
  FROM         mas FULL OUTER JOIN
              cash ON cash.date = mas.dateofissue   
  where mas.dateofissue &gt;= '2012-03-01 00:00:00.000' and mas.dateofissue &lt;= '2012-03-31 00:00:00.000' and 
  cash.date &gt;= '2012-03-01 00:00:00.000' and cash.date &lt;= '2012-03-31 00:00:00.000'
  group by mas.dateofissue
  </code></pre>
  <p>now the query works but double or triples the amounts which were aggregated
  i searched for a solution and someone told to use distinct with sum
  as</p>
  <p>sum(distinct mas.amount)</p>
  <p>but the answers were not coming correctly.</p>
  <p>I know i am not good with explaining problems but please help me out here.</p>
  <p>thanks.</p>