Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ScottBot
Starting Member
16 Posts |
 Posted - 2011-02-10 : 12:05:32
|
| I have a table with the following fields Item_ID, itemName and costPerItem.Using the costPerItem field I run a calculation to find a weekly cost based on a 5 day week and generate the answer in a new column (which I name weeklyTotal). Is there a way to add all the values in the weeklyTotal column to find a grand total of all items together using SUM?If this was an existing column in the table it would work fine, as this column(weeklyTotal) is generated I can't get it to work.Anyone with a work around or a better way?Cheers |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-10 : 12:13:07
|
| can you come up with some sample data and the required output format of that data? |
 |
|
|
ScottBot
Starting Member
16 Posts |
Posted - 2011-02-11 : 05:26:02
|
| itemName | costPerItem |Cast(costPerItem * 5 AS decimal(6 , 2)) AS || weeklyTotalwood | £5.00 | || £25.00steel | £15.00 | || £75.00plastic | £2.00 | || £10.00 ============== grandTotal £110.00 ==============the result I am looking for would be grandTotal (wood + steel + plastic) = £110.00hope this is more clear |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-11 : 05:42:20
|
| considering the table structure as followingItem_ID, itemName and costPerItem1, Wood, 5.002, Steel, 15.003, Plastic, 2.0If this is how your table structure and data is placed then check the below querySelect Sum(isnull(CostPerItem,0))*5 AS WeeklyTotalForAllItemsFrom TableName |
|
|
|
ScottBot
Starting Member
16 Posts |
Posted - 2011-02-11 : 07:26:45
|
| That has worked a charm. Appreciate your time and knowledge sharing.Thank you very much |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-11 : 07:37:01
|
| you are welcome .. CheersMIK |
|
|
|
|
|
|
Troubles with SUM