Hi guys, am having an issue with a mssql query an running in mssql 2005 server. I
have two tables debttable (customer, subcat, drugs, form, total, paid, date, balance
qty and amount as columns) and debtsum(customer, subcat, total, paid, date, balance).
Now since both tables have some columns in common, I want to list some columns from
both tables where subcat, customer and date are the same in both table but I want to
sum only distinct total, balance and paid from any of the tables. My problem is that
when I do sum, I don't get the distinct sum. Below is my query:
select debttable.drugs as DRUG, form as FORM, qty as QTY, debttable.customer as NAME, debttable.date as DATE, amount as AMOUNT, debttable.subcat as SUBCATEGORY, sum(debtsum.total) as TOTAL, sum(debtsum.paid) as PAID, sum(debtsum.balance) as BAL from debttable, debtsum where debttable.customer=debtsum.customer and debttable.balance=debtsum.balance and debttable.date=debtsum.date and debttable.total=debtsum.total and debttable.paid=debtsum.paid and debttable.subcat = debtsum.subcat and debtsum.customer ='jis' and debtsum.subcat like '%GUA%' and debttable.date between '2011-08-08' and '2011-10-09' group by drugs, amount, debttable.customer, debttable.subcat, qty, form, debttable.date