Click here to Skip to main content
15,860,861 members
Please Sign up or sign in to vote.
1.50/5 (2 votes)
See more:
Hello Experts, please read my concern carefully. below is my sql query for my C# program. Once run the sql query, I'm getting results to the datagridview. In below you can see, I have Item table and Tran2 table, my problem is, I want to do weight multiply by ActualQty of each items for the each voucher number (not the wrong way which I'm doing now, it will get different values, see below). As per below example table value, I have two items A and B. I need to multiply A from Item table weight from Trans2 table ActualQty and do the same for rest and I need to get it's sum value. See below correct way.

Please help me how to do it from one SQL statement?

VB
Correct Way (Which I need to learn)
21.31*31 = 660.61
31.22*200 = 6244
660.61+6244 = 6904.61 = Weight

VB
Wrong Way (SUM(i.Weight)*SUM(t2.ActualQty)) as Weight
21.31+31.22 = 52.53
31+200 = 231
52.53*231 = 12134.43 = Weight

table
Item Table
Itemname Weight
A        21.31
B        31.22

table
Tran2 Table
ItemName   VoucherNumber    ActualQty
A              001             31
B              001             200

SQL
select distinct t1.PartyName as Customer_Name, SUM(t2.Amount) as Bill_Amount, MAX(t2.VoucherNumber) as Invoice_Number, SUM(i.Weight)*SUM(t2.ActualQty) as Weight, SUM(i.CMB), MAX(p.RouteNo), MAX(t1.Adress3) as City
from Item i, Party p, Tran1 t1, Tran2 t2
Where t1.VoucherNumber=t2.VoucherNumber and t2.ItemName=i.Itemname and p.PartyName=t1.PartyName and p.RouteNo='" + comboBox1.Text + "' and t1.LoadingStatus IS NULL GROUP BY t1.PartyName
Posted

1 solution

Shouldn't that just be SUM(i.Weight* t2.ActualQty)?
w     q
2     3
4     5
SUM(w) * SUM(q) = (2 + 4) * ( 3 + 5) = 6 * 8  = 48
SUM(w * q)      = (2 * 3) + (4 * 5)  = 6 + 20 = 26
 
Share this answer
 
Comments
abdulsafran 17-Nov-14 14:29pm    
Very much simple answer. Thank you very much OriginalGriff.......
OriginalGriff 17-Nov-14 14:31pm    
You're welcome!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900