| Author |
Topic |
|
besadmin
Posting Yak Master
116 Posts |
 Posted - 2010-11-16 : 16:15:01
|
Hey Friends, I need some help. Say you have a table like soID OrderNo ShipmentID InvoiceTotal Shipment Total1 1 1 10.00 10.002 2 2 05.50 11.50 3 3 2 06.00 11.50 So, how do i compute that shipment total column? They will be entering the InvoiceTotal on a Web form, then I want the Shipment Total to Sum up according to the Invoice Total if they are on the same ShipmentID as shown above, orderID 2 and 3 are on shipmentid 2 so their total is the SUM of each invoice.Any help with this is greatly appreciate please.Thanks soo much in advance for any replies!Later Friends! |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2010-11-16 : 16:26:32
|
| Off the top of my head (Haven't tested it):SELECT ID, OrderNo, ShipmentID, InvoiceTotal, SUM(InvoiceTotal) AS "Shipment Total"FROM tableGROUP BY ID, OrderNo, ShipmentID, InvoiceTotal -Chad |
 |
|
|
besadmin
Posting Yak Master
116 Posts |
Posted - 2010-11-16 : 16:34:02
|
| Didn't quite work as i was hoping. It just returned the invoice total in the shipment total. like 2 would be 5.50 and 5.50 and 3 would be 6.00 and 6.00 instead of 5.50 11.50 and 6.00 11.50. Thanks a lot for the reply though, i see where you were goin. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2010-11-16 : 16:40:27
|
| Ah yes, because Invoice Total is in the group. Sorry, try this:SELECT table.ID, table.OrderNo, table.ShipmentID, table.InvoiceTotal, temp.[Shipment Total]FROM (SELECT ShipmentID, SUM(InvoiceTotal) AS "Shipment Total"FROM tableGROUP BY ShipmentID) tempJOIN table on temp.ShipmentID = table.ShipmentIDAgain, no test, but I think that will do it.-Chad |
|
|
|
besadmin
Posting Yak Master
116 Posts |
Posted - 2010-11-16 : 16:47:05
|
| Ah thanks a ton chadmat! That works great. I will have to figure out how i am going to implement this in my current situation, but that is pretty much what i was looking for.Thanks again for your help!LAter Friends! |
|
|
|
besadmin
Posting Yak Master
116 Posts |
Posted - 2010-11-17 : 13:55:31
|
| OK Friends, I need a bit of additional help please.I want to do this same thing, the above query worked = )However, I need to do it each time someone inserts a Invoice Total From a Shipment. So both columns will be blank. If they are on the same ShipmentID I want to add the invoice totals together to make the shipment total column populated. So insert $10 would make the total column 10, then inser $15, to total column would become $25 and so on. I hope I am being clear. Any help is greatly appreciate. I think I can probably just use something like the above query to do an insert or something correct?Thanks again to any replys!!Later Friends! |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2010-11-17 : 15:03:25
|
| You could use a trigger to figure it out, and populate the total.-Chad |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
besadmin
Posting Yak Master
116 Posts |
Posted - 2010-11-17 : 16:29:25
|
| hmm, both interesting ideas. I am not sure if the view will work. it's a little messy i know, since i am using a web page for the interfacea select by number populates a form, and others on the same shippingid are in a gridview. you can select each one and enter the individual cost, and i want the total to update at that time, then repopulate.ChadMat, or anyone else = ). How would I go about using a trigger? I haven't really used them before?Thanks again to all!! |
|
|
|
|
Having Total Column For Same Shipment