[SQL]: GROUP BY Problems and Explanation.

[Ruslan Zasukhin]

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

Hi Frank,

I have good and bad news for you.
GOOD NEWS are that Valentina do this query correct!
BAD NEWS are that mySQL and Sybase are wrong on this query.

Read more at the end of this letter:

>>> 2. Outer Join (unexpected result):
>>> 
>>> SELECT t.Type, count(*)
>>> FROM Type t, pattern p
>>> WHERE t.RecID *= p.TypePtr
>>> GROUP BY t.Type
>>> 
>>> Note: If this returns 1 record for due to the JOIN table, then what if there
>>> really was only
>>> 1 joining record in the Pattern table? You would get the same result.
> 
>> For INNER join and outer join.
>> Hmm, may be I am down, but I do not see why Valentina results are wrong.
>> 
>> On outer join you get
>> 
>>   Type A      1
>>   Type A      2
>>   Type A      3
>>   Type A      4
>>   Type B      NULL
>>   Type C      NULL
>> 
>> We can consider this as real Table and now we do query for it
>> 
>> SELECT Type, count(*)
>> FROM T
>> GROUP BY Type
>> 
>> What result you expect now?!!
>> For such SINGLE TABLE and such query.
>> 
>> It seems result must be
>> 
>>   Type A      4 
>>   Type B      1
>>   Type C      1 
>> 
>> 
>> Then why you expect OTHER result for 2 tables outer join ???????????

> Because Sybase and MySQL would return this:
> 
> Type A   4
> Type B   0
> Type C   0
> 
> Think about it for a bit, it makes sense to me. But it also is how the
> other databases handle it.

Frank, mySQL and Sybase are wrong here!!!

We have test this question on MS SQL Server, and it return results exactly
as Valentina. Again and again. For me results of Valentina and SQL Server do
have sense. They follow rules. Read my explanation above why and how this
query must be resolved. Count(*) in your query must coount GROUPS in the
result outer join.


As far as I see, you want count related records in the second table, yes?
I have ask self how to do this in SQL Server.
Answer is:

 SELECT t.Type, count(t2.Anyfield)
 FROM Type t, pattern p
 WHERE t.RecID *= p.TypePtr
 GROUP BY t.Type
   
SQL Server for this query returns

 Type A   4
 Type B   0
 Type C   0

It very looks to be something NON standard also. But have sense by logic.
Because count(fld) DO NOT counts records with NULL values (look on outer
join last 2 records). So this do follow rules.

What strange here is that they count on a field that NOT present in join
table...so this looks to be hacking feature of SQL Server, and as for me I
like it...and probably will make for Valentina also..


-- 
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
-------------------------------------------------------------