Re: Simple SQL?

Goran Sliskovic wrote:
>
> Larry Coon <larry_at_assist.org> wrote in message
> news:3B044E5E.6F37_at_assist.org...
>
> > Then maybe...
> >
> > SELECT a, SUM(b2)/COUNT(c2), SUM(c2)/COUNT(b2)
> > FROM tblA, tblB, tblC
> > WHERE a = b1 AND a = c1
> > GROUP BY a
> >
> >
> > Larry Coon
> > University of California
> > larry_at_assist.org
> > and lmcoon_at_home.com
>
> Still no good :).
> Now it will return 1/2sum(b2) and 1/3sum(c2).
> if you really want to do it without subquery, and tables have primary keys
> (and they should):
>
> SELECT a, SUM(b2)/COUNT(*)*count(distinct tblB.pk),
> SUM(c2)/COUNT(*)*count(distinct tblC.pk)
> FROM tblA, tblB, tblC
> WHERE a = b1 AND a = c1
> GROUP BY a

Very nice solution! It can be simplified a litte:

 SELECT a, SUM(b2)/COUNT(DISTINCT tblC.pk), SUM(c2)/COUNT(DISTINCT tblB.pk)  FROM tblA, tblB, tblC
 WHERE a = b1 AND a = c1
 GROUP BY a;

In fact, since the columns of tblB are only b1 and b2 you know that given a certain 'a' all the b2 values in tlbB are distinct. So you can make it the following:

 SELECT a, SUM(DISTINCT b2), SUM(DISTINCT c2)  FROM tblA, tblB, tblC
 WHERE a = b1 AND a = c1
 GROUP BY a;

I don't think it can get much simpeler. :-)

-- 
  Jan Hidders