[SQL]: GROUP BY Problems

[Frank Schima]

On Friday, June 13, 2003, at 02:34  PM, Ruslan Zasukhin 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.

> > SQL (i.e. no
> > WHERE clause) correctly returns one record:
> > Type A, 4

This is the key result Ruslan. The inner join correctly returns 1 record (showing the 4 
records that all link to the same Type). Thus the other Types must have no linked 
records in the Patterns table or they would have shown up. The point of an outer join is 
to show all cases, even if there are no corresponding records (count(*) of 0).

> > Another problem occurs if I try to also select the RecID, then it does not
> > work correctly. I brought this up before but I don't remember the resolution.
> > Maybe it was to wait for version 2 of the kernel?
> 
>     SELECT RecID, t.Type, count(*)      ?
> 
> But this is not correct, because RecID is not under GROUP BY.
> Correct?

Right, but I tried to put it there too. Can we only have one term in the GROUP BY?
Here is the complete SQL that (also) does not work.

SELECT t. RecID, t.Type, count(*)
FROM Types t, Patterns p
WHERE t.RecID *= p.TypePtr
GROUP BY t.RecID, t.Type

It returns 2 columns instead of 3. 


-Frank


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