Comparing Tables

[Ciliate]

Longish post.

I have two tables, each about 35,000 rows, derived from different data sources. I need to compare that the calculations based on each of the tables yield equal results. For example, as a quality check, I need to sum "Sales" for each table, and compare the results, which should be equal.


table_1

Region | Sales
=================
NE 1000
SW 1250
.
.
.
n

table_2

Region | Sales
=================
NE 1000
SW 1250
.
.
.
n


I've tried the following query:

SELECT SUM(t1.sales), SUM(t2.sales)
FROM table_1 t1, table_2 t2

However, I wind up with a wild number, equal to the actual sum of sales for one table, multiplied by the number of rows in the other table.

I have similar calculations and comparisons that I need to make between these two tables, and need to develop a consistent way to handle these kinds of SQL manipulations.

Thanks.

 

[r937]

select 'table 1 total: ', sum(sales)
  from table_1 
[b]union all[/b]
select 'table 2 total: ', sum(sales)
  from table_2

r937.com | rudy.ca

 

[Ciliate]

thanks! works like a charm.
Looks like I need to read up on UNION, huh?

 

[Ciliate]

Thanks, again. While thinking on this some more, and while I have your attention, let me deepen the inquiry. Suppose I wanted to calculate the two sums, subtract one from the other, display all three values (the two sums and the difference) and test the result? And, suppose I wanted to store the values persistently?

So

1. Calculcate: SUM(table 1) - SUM(table 2) = difference
2. Compare difference to some constant
3. Write the results to a persistent table.

 

[r937]

you don't need to store the sums persistently

hold on a sec

r937.com | rudy.ca

 

[Ciliate]

Forget about the other wish list - just me thinking out loud. Wrong place, wrong time ... bad ciliate!

The code works well, but gives me a result like this:

---------------------------
table_1 total | <a sum>
---------------------------
table_2 total | <a sum>
---------------------------

I'd like this to look more like:

table_1 total | table_2 total |
--------------------------------
<a sum> <a sum>

to make the calculations easier. I'll be logging these kinds of results over time, and I am struggling with how to repeat calculations betweens pairs of values stored in a single column.

What am I missing?

 

[r937]

insert 
  into archives
     ( archivedate, t1total, t2total )
select current_date, t1total, t2total
  from ( select sum(total) as t1total 
           from t1 ) as sum1
cross
  join ( select sum(total) as t2total
           from t2 ) as sum2

r937.com | rudy.ca