August 17, 2009 at 1:49 pm
Good Day Guys,
I have two tables that look something like the ddl info below
CREATE TABLE #Merch_Act
(
[day] int,
Sales Money,
AvgSale Money,
Goods int,
Goodsbought int
)
insert into #Merch_Act
values(1, 200, 40, 300, 150)
insert into #Merch_Act
values(2, 250, 50, 500, 200)
insert into #Merch_Act
values(3, 300, 60, 400, 400)
insert into #Merch_Act
values(4, 400, 70, 330, 310)
insert into #Merch_Act
values(5, 500, 80, 200, 110)
CREATE TABLE #Merch_Act2
(
[Day] int,
Sales Money,
AvgSale Money,
Goods int,
Goodsbought int
)
insert into #Merch_Act2
values(1, 200, 35, 250, 100)
insert into #Merch_Act2
values(2, 250, 45, 450, 150)
insert into #Merch_Act2
values(3, 300, 55, 350, 350)
insert into #Merch_Act2
values(4, 400, 65, 280, 260)
insert into #Merch_Act2
values(5, 500, 75, 150, 60)
Problem,
I want to make these tables one table that will contain a sum of all the columns(except day). So I need to see (#merch_act.sales + #merch_act2.sales) in the new table as "sales", avg (#merch_act.AvgSale + #merch_act2.AvgSale) as "AvgSale", (#merch_act.goods + #merch_act2.goods) as "goods",
(#merch_act.goodsbought + #merch_act2.goodsbought) as "goodsbought", all for the same day number.
Here is the ddl of what I want to see
CREATE TABLE #Merch_Act_Total
(
[Day] int,
Sales Money,
AvgSale Money,
Goods int,
Goodsbought int
)
insert into #Merch_Act_Total
values(1, 400, 37.5, 550, 250)
insert into #Merch_Act_Total
values(2, 500, 47.5, 950, 350)
insert into #Merch_Act_Total
values(3, 600, 57.5, 750, 750)
insert into #Merch_Act_Total
values(4, 800, 67.5, 610, 570)
insert into #Merch_Act_Total
values(5, 1000, 77.5, 350, 170)
select * from #Merch_Act
select * from #Merch_Act2
select * from #Merch_Act_Total
What is the best way of going about this? Thanks in advance for any form of help.
August 17, 2009 at 2:03 pm
Not sure if it's the best way, but here's my solution:
INSERT INTO #Merch_Act_Total
SELECT [Day]
, SUM( Sales )
, AVG( AvgSale )
, SUM( Goods )
, SUM( Goodsbought )
FROM
( SELECT *
FROM #Merch_Act
UNION ALL
SELECT *
FROM #Merch_Act2
) AllResults
GROUP BY AllResults.[Day]
August 17, 2009 at 3:53 pm
Thanks for the feedback, it worked fine. I just need one more modification. What if the same tables do not return the same number of columns, is it possible to add columns on both tables together based on the day they have in common? For example if table has 5 days and the other has 4 days, is it possible to add columns for both tables for the first 4 days they have in common and then just add 0 to the 5th one? Here is the ddl info for what I mean.
ddl for info i have
CREATE TABLE #Merch_Act
(
[day] int,
Sales Money,
AvgSale Money,
Goods int,
Goodsbought int
)
insert into #Merch_Act
values(1, 200, 40, 300, 150)
insert into #Merch_Act
values(2, 250, 50, 500, 200)
insert into #Merch_Act
values(3, 300, 60, 400, 400)
insert into #Merch_Act
values(4, 400, 70, 330, 310)
insert into #Merch_Act
values(5, 500, 80, 200, 110)
CREATE TABLE #Merch_Act2
(
[Day] int,
Sales Money,
AvgSale Money,
Goods int,
Goodsbought int
)
insert into #Merch_Act2
values(1, 200, 35, 250, 100)
insert into #Merch_Act2
values(2, 250, 45, 450, 150)
insert into #Merch_Act2
values(3, 300, 55, 350, 350)
insert into #Merch_Act2
values(4, 400, 65, 280, 260)
ddl for info that i need
CREATE TABLE #Merch_Act_Total
(
[Day] int,
Sales Money,
AvgSale Money,
Goods int,
Goodsbought int
)
insert into #Merch_Act_Total
values(1, 400, 37.5, 550, 250)
insert into #Merch_Act_Total
values(2, 500, 47.5, 950, 350)
insert into #Merch_Act_Total
values(3, 600, 57.5, 750, 750)
insert into #Merch_Act_Total
values(4, 800, 67.5, 610, 570)
insert into #Merch_Act_Total
values(5,500, 80, 200, 110)
Thank you
August 17, 2009 at 8:13 pm
You ran Julie's code... you should know that it does. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2009 at 9:41 am
Worked fine, you are right Jeff. Thanks guys !!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply