March 1, 2007 at 5:10 am
I have created a simple select script that sums the value in a column which i then want to return the out put into another field in another table but dont know how to do it.
I have created the select script (which works) but dont know how to incorporate it into the INSERT script?
March 1, 2007 at 6:03 am
Stuart,
This would work (I've included working tables etc)
But the insert at the end works, you would just need to modify it for your tables.
create table dbo.sumtest
(
ID int identity,
Type varchar(10),
Amount int
)
insert into dbo.sumtest (type, amount)
Values ('Widget', 10)
insert into dbo.sumtest (type, amount)
Values ('Widget', 12)
insert into dbo.sumtest (type, amount)
Values ('Widget', 20)
insert into dbo.sumtest (type, amount)
Values ('Widget', 30)
insert into dbo.sumtest (type, amount)
Values ('Widget', 1)
insert into dbo.sumtest (type, amount)
Values ('Bulb', 8)
insert into dbo.sumtest (type, amount)
Values ('Bulb', 30)
insert into dbo.sumtest (type, amount)
Values ('Bulb', 27)
insert into dbo.sumtest (type, amount)
Values ('Fork', 4)
insert into dbo.sumtest (type, amount)
Values ('Fork', 2)
select type, sum(Amount) as Total
from dbo.sumtest
group by Type
insert into dbo.sumtotal (type, total)
select type, sum(Amount) as Total
from dbo.sumtest
group by Type
select * from dbo.sumtotal
Many thanks,
Rodney.
March 1, 2007 at 6:25 am
Great thankyou
March 1, 2007 at 6:58 am
A slight variation on this theme now.
I have my first script
INSERT INTO FINANCEFLASH
(recharge_no)
SELECT count(*) as recharge_no
FROM ((("Adonix"."THIERRY"."GACCENTRY" "GACCENTRY" INNER JOIN "Adonix"."THIERRY"."GACCENTRYD" "GACCENTRYD" ON ("GACCENTRY"."TYP_0"="GACCENTRYD"."TYP_0") AND ("GACCENTRY"."NUM_0"="GACCENTRYD"."NUM_0")) LEFT OUTER JOIN "Adonix"."THIERRY"."SINVOICE" "SINVOICE" ON ("GACCENTRY"."NUM_0"="SINVOICE"."NUM_0") AND ("GACCENTRY"."ORIMOD_0"="SINVOICE"."ORIMOD_0")) LEFT OUTER
which works great
I have a second script
INSERT INTO FINANCEFLASH
(recharge_value)
SELECT SUM(GACCENTRYA.AMTLOC_0 * GACCENTRYD.SNS_0) AS recharge_value
FROM Adonix.THIERRY.GACCENTRY GACCENTRY INNER JOIN Adonix.THIERRY.GACCENTRYD GACCENTRYD ON GACCENTRY.TYP_0 = GACCENTRYD.TYP_0 AND GACCENTRY.NUM_0 = GACCENTRYD.NUM_0 LEFT OUTER JOIN Adonix.THIERRY.SINVOICE SINVOICE ON GACCENTRY.NUM_0 = SINVOICE.NUM_0 AND GACCENTRY.ORIMOD_0 = SINVOICE.ORIMOD_0 LEFT OUTER JOIN Adonix.THIERRY.GACCENTRYA GACCENTRYA ON GACCENTRYD.NUM_0 = GACCENTRYA.NUM_0 AND GACCENTRYD.TYP_0 = GACCENTRYA.TYP_0 AND GACCENTRYD.LIG_0 = GACCENTRYA.LIG_0 LEFT OUTER JOIN Adonix.THIERRY.SINVOICEV SINVOICEV ON SINVOICE.NUM_0 = SINVOICEV.NUM_0
WHERE (GACCENTRYD.CNA_0 >= '63000') AND (GACCENTRYD.CNA_0 <= '63999') AND (GACCENTRYD.TYP_0 <> 'NEW') AND (GACCENTRYD.CPY_0 = '001')
but rather i want to combine the two so it inserts one line with both values on the one line rather than one after the other?
March 2, 2007 at 1:54 am
I presume that the FROM and WHERE clauses are the same for both statements?
INSERT INTO FINANCEFLASH(recharge_no, recharge_value)
SELECT COUNT(*), SUM(GACCENTRYA.AMTLOC_0 * GACCENTRYD.SNS_0)
FROM ...
You do not need the "AS" alias. The order of fields is dictated by the specification of the column names with the FINANCEFLASH target table.
March 2, 2007 at 3:29 am
They are in this case but i have some where they are different - how would i do those?
March 7, 2007 at 4:07 am
Stuart,
I think you have two options. Both rely on you haveing some kind of key that you can join the results together with.
1. Would be in two parts:
Insert into based on first query
Then an update based on the second query with a join based on the key. Between the second query and the table you are inserting into.
2. Using a join on 2 derived tables. And inserting from that. Because your where clauses could be different you may have use a full outer join - you may have items in one query that aren't in the other and visa versa. (If this was the case the 1st way - two part insert and update could "lose" records from the second query).
If you need further help or a script to show what I mean, ask and I'll see what I can pull together for you. Some test data from you would help as well.
Regards,
Rodney.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply