sum() function in sql doubles the amount when used with multiple table

Question

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

i have 2 tables

1. cash
2. mas

the structure of the tables is like

mas table

id - int

dateofissue - datetime

amount - float

---

cash table

id - int

date - datetime

amount - float

---

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

the structure of the report will be like

---

date------------------ master amount --------------- cash amount

2/3/2012-------------- 2000 ------------------------ 1000

3/3/2102-------------- 4000 ------------------------ 200

...

....

---

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.

i am using a sql query like

     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 >= '2012-03-01 00:00:00.000' and mas.dateofissue <= '2012-03-31 00:00:00.000' and 
cash.date >= '2012-03-01 00:00:00.000' and cash.date <= '2012-03-31 00:00:00.000'
group by mas.dateofissue

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

sum(distinct mas.amount)

but the answers were not coming correctly.

I know i am not good with explaining problems but please help me out here.

thanks.

Answer 1

It's not SUM() that's causing the doubling, it's the JOIN. SUM() is done after you're done your JOIN, and your JOIN appears to be matching one record from one talbe to two records from the other table.

Either, you need to de-duplicate your tables to prevent the 1:2 matching in your join, or pre-aggregate your tables...

Pre-process option:

SELECT
  COALESCE(mas.dateofissue, cash.date) AS date,
  cash.amount                          AS CashAmount,
  mas.amount                           AS DraftAmount
FROM
  (SELECT dateofissue, SUM(amount) as amount FROM mas GROUP BY dateofissue) AS mas
FULL OUTER JOIN
  (SELECT date, SUM(amount) as amount FROM cash GROUP BY date) AS cash
     ON cash.date = mas.dateofissue
WHERE
      (mas.dateofissue >= '2012-03-01 00:00:00.000' OR mas.dateofissue IS NULL)
  and (mas.dateofissue <= '2012-03-31 00:00:00.000' OR mas.dateofissue IS NULL)
  and (cash.date       >= '2012-03-01 00:00:00.000' OR cash.date       IS NULL)
  and (cash.date       <= '2012-03-31 00:00:00.000' OR cash.date       IS NULL)

Identify which table has the duplicates:

SELECT dateofissue, COUNT(*) FROM mas  GROUP BY dateofissue HAVING COUNT(*) > 1
SELECT date,        COUNT(*) FROM cash GROUP BY date        HAVING COUNT(*) > 1

EDIT

I've also noticed that you have a FULL OUTER JOIN and a filter in the WHERE clause; this forces it back into being an INNER JOIN.

So, I've changed the above query to actually work as a FULL OUTER JOIN, there are other ways to do it in your case, such as doing the filter in the sub-queries, but the changes I made should help see how to filter a FULL OUTER JOIN :)