[SQL]: GROUP BY Problems and Explanation.

[Frank Schima]

On Saturday, June 14, 2003, at 10:23  AM, Ruslan Zasukhin wrote:
> 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..

That makes sense. Please add this!! As long as there is a way to get the number of real 
records in a related table (using an outer join) then I will be happy. :^)


Best regards,
Frank

---
Frank Schima                <mailto:frank-list2 at-sign mindstarprods.com>
Independent Consultant
Gaithersburg, MD USA        <http://www.ci.gaithersburg.md.us/>