Brian Peasland wrote:
> > insert into dest_table (column_z, column_1, column_2,...)
> > select COUNT(*), column_1, column_2,..., column_N,
> > SUM(column_a), MIN(column_b), MAX(column_c),...
> > from source_table
> > group by column_1, column_2,...,column_N
> >
> > I would expect that SUM(dest_table.column_z) would equal
> > COUNT(source_table.*), but they are off by about 40 rows - in other
> > words, 40 rows in source_table are not being included in the select.
>
> The SUM function will total all of the numbers in that column. The COUNT
> function will give the number of rows. Why is it expected that both
> would be equal? Unless dest_table.column_z contains all 1's, then the
> SUM and the COUNT will not be equal.
>
> I must be missing something....
In the insert, each dest_table.column_z value contains
COUNT(source_table.*) - in other words, the number of source_table
records grouped together to create that dest_table record. Since there
is no restriction on which source_table records are being selected, the
sum of all of the column_z values in dest_table should equal the number
of records in source table. That is,
SELECT SUM(column_z) FROM dest_table
and
SELECT COUNT(*) FROM source_table
should return the same number.
However, the first one is smaller than the second one, indicating that
some source_table records are not being included when being grouped
into dest_table records.
Received on Mon Apr 24 2006 - 11:47:25 CDT
