[SQL]: GROUP BY Problems

[Ruslan Zasukhin]

on 6/13/03 22:01, Frank Schima at frank-list2 at mindstarprods.com wrote:

>>> So I get:
>>> Type A, 4
>>> Type B, 1
>>> Type C, 1
>>> 
>>> But I should get:
>>> Type A, 4
>>> Type B, 0
>>> Type C, 0
>>> 
>>> There are only 4 records in the Patterns table! The inner join version of
>>> the
> 
>> So what?
>> But count(*) counts records in the JOIN table, correct?
> 
> Count(*) returns the number of records in each GROUP. 4 records in that table
> means 
> the Sum of the Count()s should have been 4 - not 6.
> 
>> Frank, please test this query on other DBMS,
>> It seems to me result is correct.
> 
> I tested on another database and got a similar unexpected result.

Why unexpected, Frank?!

Look, at first you do OUTER JOIN, and you get JOIN TABLE:

    Type A      1
    Type A      2
    Type A      3
    Type A      4
    Type B      0
    Type C      0
   
GROUP BY operator sort this table and count(*)
counts records in each group.

But Frank, JOIN TABLE have 6 records already !!

So result is correct.


-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------