Issues with MSSQL query

[SAINTJAB]

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

[jbisono]

I don't quite understand your question, when you say "I want to
sum only distinct total, balance and paid", you mean that you will have two different total, two different balance, etc. So you will have a result like,

CUSTOMER  TOTAL_FROM_DEBTTABLE   TOTAL_FROM_DEBTSUM
  SOME          500.00                 250.00

[SAINTJAB]

I don't quite understand your question, when you say "I want to
sum only distinct total, balance and paid", you mean that you will have two different total, two different balance, etc. So you will have a result like,

CUSTOMER  TOTAL_FROM_DEBTTABLE   TOTAL_FROM_DEBTSUM
  SOME          500.00                 250.00

What I mean is that, when you take total column from only debttable, you can have some values like 12, 16, 20, 12 and these same values are in debtsum. I want it such a way if I sum total from one of the tables, the same should be 12 + 16 + 20 not 12 + 16 + 20 + 12.

[jbisono]

I'm assuming that all those transaction is for one particular customer, think this is what you need, i just did it with the total column, let's keep it simple.

SELECT customer, subcat, SUM(total) AS GranTotal
FROM (SELECT DISTINCT debttable.customer, debttable.subcat, debttable.total
      FROM debttable INNER JOIN debtsum ON debttable.customer = debtsum.customer    AND debttable.subcat = debtsum.subcat) AS TEST
GROUP BY customer, subcat

I used a derived table for doing that.

Try that and see if you have your result, but I believe that you have to revise your DB Design. Let me know if you need any advise.

Regards..

[SAINTJAB]

I'm assuming that all those transaction is for one particular customer, think this is what you need, i just did it with the total column, let's keep it simple.

SELECT customer, subcat, SUM(total) AS GranTotal
FROM (SELECT DISTINCT debttable.customer, debttable.subcat, debttable.total
      FROM debttable INNER JOIN debtsum ON debttable.customer = debtsum.customer    AND debttable.subcat = debtsum.subcat) AS TEST
GROUP BY customer, subcat

I used a derived table for doing that.

Try that and see if you have your result, but I believe that you have to revise your DB Design. Let me know if you need any advise.

Regards..

Thanks Jbisono but some of the values from the sum(total) are still repeating. You see, I have some duplicates in the total column but I need to sum only distinct values. Below is the modified version of your query but am still getting duplicates.

SELECT drugs as DRUG,form as FORM,qty as QTY, amount as AMOUNT,customer as NAME, subcat as SUBCATEGORY, SUM(distinct total) AS TOTAL,
sum(distinct paid) as PAID, sum(distinct balance) as BAL, date as DATE FROM (SELECT DISTINCT debttable.customer,debttable.drugs, debttable.form,
debttable.subcat,debttable.total, debttable.date, debttable.paid,debttable.balance, debttable.qty, debttable.amount
FROM debttable INNER JOIN debtsum ON debttable.customer = debtsum.customer AND debttable.subcat = debtsum.subcat) AS TEST
where test.customer ='YOBO' and test.subcat like '%A.A%' and test.date between '2011-09-08' and '2011-10-10' GROUP BY drugs,form,
customer, subcat, date, qty, amount

[adam_k]

Friend, if you use distinct you are only adding the values that are not the same. This means that if I buy the same product twice (in 2 different transactions) you'll see that I owe you the ammount for 1 product?
Do the duplicate values exist in the tables or they are generated when you join the tables (cartessian product)?
If they exist then you need to make the records unique by puttin gin the game fields like transaction number, date or something similar.
If the join creates them, then you need to find better keys.

[SAINTJAB]

Friend, if you use distinct you are only adding the values that are not the same. This means that if I buy the same product twice (in 2 different transactions) you'll see that I owe you the ammount for 1 product?
Do the duplicate values exist in the tables or they are generated when you join the tables (cartessian product)?
If they exist then you need to make the records unique by puttin gin the game fields like transaction number, date or something similar.
If the join creates them, then you need to find better keys.

Yeah, am trying to sum only distinct values. There are duplicates in the tables and my aim is to sum only the distinct ones.I had now even change the query to use only one table but am still getting duplicates in the result.

select drugs as DRUG,amount as AMOUNT,form as FORM, qty as QTY,customer as NAME,subcat as SUBCATEGORY,date as DATE,sum(distinct total) as TOTAL from debttable where customer ='YOBO' and subcat like '%A.A%' and debttable.date between '2011-08-08' and '2011-10-09'
group by drugs, amount, qty, form,customer, subcat,date. The problem is from the drugs in the query since that customer might have bought same drug more than ones within the selected dates. What is want is to have sum of all the distinct totals to show for all the drugs. Like this

Drug Amount Form Qty Name Subcat Date Total
Para 2.0 Cream 3 YOBO CAT 2011-09-10 12
Cus 1.7 Cap 4 YOBO CAT 2011-10-10 12