select sum(qty*cost) as totcost

,sum(qty) as totqty

,sum(qty*cost)/sum(qty) as newtot

from yourtable

where 1=1

and left(item_num,1) = 1

this will give you the calculations

im still trying to work out the rest.

Solved

Posted on 2011-05-04

sql server 2008, I have recipes for a batch of a product, I need help with the sql to do the calculation to be inserted in another table (archiving the cost for history).

item_num ingred qty cost

1 888 400 .0432

1 889 3000 .056

1 890 20 .76

13 777 30 .032

13 666 400 .031

in English:

1) for all items starting with the number 1

2) for the costs of each line qty*cost (e.g. 400 * .0432 = 17.28)

3) Add the line costs for each product (e.g. 17.28 + 168 + 15.2 = 200.48)

4) Divide the total cost by the sum of total qty (200.48 / 3420) = .05862

5) And then insert the item number, date and #4 into another table.

item_num ingred qty cost

1 888 400 .0432

1 889 3000 .056

1 890 20 .76

13 777 30 .032

13 666 400 .031

in English:

1) for all items starting with the number 1

2) for the costs of each line qty*cost (e.g. 400 * .0432 = 17.28)

3) Add the line costs for each product (e.g. 17.28 + 168 + 15.2 = 200.48)

4) Divide the total cost by the sum of total qty (200.48 / 3420) = .05862

5) And then insert the item number, date and #4 into another table.

3 Comments

select sum(qty*cost) as totcost

,sum(qty) as totqty

,sum(qty*cost)/sum(qty) as newtot

from yourtable

where 1=1

and left(item_num,1) = 1

this will give you the calculations

im still trying to work out the rest.

select * from table where item_num like '1%'

2) for the costs of each line qty*cost (e.g. 400 * .0432 = 17.28)

select item_num, ingred, qty, cost, qty*cost as totalcost

from table

where item_num like '1%'

3) Add the line costs for each product (e.g. 17.28 + 168 + 15.2 = 200.48)

select sum(qty*cost)

from table

where item_num like '1%'

4) Divide the total cost by the sum of total qty (200.48 / 3420) = .05862

select sum(qty*cost)/count(*)

from table

where item_num like '1%'

5) And then insert the item number, date and #4 into another table.

insert into table2 (item_num, date, tcost)

select item_num, getdate(), sum(qty*cost)/count(*)

from table

where item_num like '1%'

```
SELECT item_num, getdate() 'Date', cast(sum(qty * cost) / sum(qty) as decimal(10,5)) Rate
INTO #NewTable
FROM [Test2008].[dbo].[Ingr]
WHERE cast(item_num as varchar(50)) like '1%'
GROUP BY item_num
```

Result for your sample data:

```
item_num Date Rate
---------------------- ----------------------- ---------------------------------------
1 2011-05-04 14:04:50.167 0.05862
13 2011-05-04 14:04:50.167 0.03107
```

Title | # Comments | Views | Activity |
---|---|---|---|

vcenter 6 u2 install question | 1 | 59 | |

Need to fix 3 LEFT JOIN on same column | 24 | 37 | |

IF SQL Statement Access SQL | 6 | 38 | |

Access left join query | 5 | 18 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**19** Experts available now in Live!