Combine and total two tables

[bwgc]

Ok, I have two database tables with...

column 1 a date field, and column 2 an amount field.

 

I'd like to write a query which will combine and sum the two tables by date giving me one table with a date field and combined total for that date.

 

I can do a "union all" query, but that gives me two rows for each date (if both tables have amounts for the same date).

 

I suppose I could write two queries and end up with two datatables, then combine the two datatables - but I don't see an easy way to do that.

 

I've been thinking too much about this - can someone offer a suggestion?

 

Thanks - Geoff

[Joe Mamma]

Ok, I have two database tables with...

column 1 a date field, and column 2 an amount field.

 

I'd like to write a query which will combine and sum the two tables by date giving me one table with a date field and combined total for that date.

 

I can do a "union all" query, but that gives me two rows for each date (if both tables have amounts for the same date).

 

I suppose I could write two queries and end up with two datatables, then combine the two datatables - but I don't see an easy way to do that.

 

I've been thinking too much about this - can someone offer a suggestion?

 

Thanks - Geoff

SQL Server?

[Nerseus]

You'll want a join between the tables with a GROUP BY on the date part you want (if you have no time portion you can just use the date field itself). The group by allows you to do the SUM(amount) per the group by (date).

 

-ner

[bwgc]

You'll want a join between the tables with a GROUP BY on the date part you want (if you have no time portion you can just use the date field itself). The group by allows you to do the SUM(amount) per the group by (date).

 

-ner

 

Never used "joins" much, but if I do:

 

select table1.date, sum(table1.amount), sum(table2.amount)

join on table2.date = table1.date

Group by 1

 

...I get totals overstated by a multiple of the number of records in table2

 

Again, I apologize for my lack of knowledge with joins and if this is the wrong forum - I apologize for that also, but the forum description does read "Topics include: ADO.NET, SQL, XML structures and schemas, Crystal Reports, and other reporting tools".

 

Thanks - Geoff

[Nerseus]

Ah, you said you wanted a combined total per the date - not separate totals.

 

If it were me, I'd use some temp tables. They're easy to use and understand, especially for reports that often need misc data grouped together. There's likely a way to do do it one select, but I'm too lazy to think about it right now. Also, temp tables can often be faster since you can avoid a bunch of joins to get what you want. I'm

 

If you use SQL Server, something like this:

CREATE TABLE #temp2 (MyDate datetime, Amount2 money)
CREATE TABLE #temp (MyDate datetime, Amount1 money, Amount2 money)

INSERT INTO #temp (MyDate, Amount1)
SELECT [date], sum([Amount])
FROM Table2
GROUP BY [date]

INSERT INTO #temp2 (MyDate, Amount2)
SELECT [date], sum([Amount])
FROM Table3
GROUP BY [date]

UPDATE #temp
SET Amount2 = #temp2.Amount2
FROM #temp2
WHERE #temp.MyDate = #temp2.MyDate

INSERT INTO #temp (MyDate, Amount2)
SELECT #temp2.MyDate, #temp2.Amount2
FROM #temp2
WHERE NOT EXISTS (select * from #temp where #temp.MyDate = #temp2.MyDate)

SELECT * FROM #temp

drop table #temp
drop table #temp2

 

-ner

[bwgc]

Thanks for your suggestions. Actually, in this particular instance, I ended up doing a "union select all", then writing a simple (4 lines of code) routine (after the table is returned) to combine totals and delete the dupicate date rows in the datatable.

I thought I was missing an easy way to do this with some sort of "join", but obviously not.

 

Thanks - Geoff