Re: Simple SQL?

Vadim Tropashko wrote:
> In article <9ec5qs$l7i$1_at_news.tue.nl>, Jan Hidders says...
> >
> >Vadim Tropashko wrote:
> >> In article <9ebtlf$hvf$1_at_news.tue.nl>, Jan Hidders says...
> >> >
> >> >No? What was wrong with Goran's solution? Or mine?
> >> >
> >> When combining aggregates from three tables you'll have to
> >> multiply counts -- not pretty;-)
> >
> >Which solution are you talking about here? In my version of Goran's
> >solution there was no multiplication and also not in my own solution.
>
> No, if the total number of tables becomes 4, Goran's solution becomes:
>
> SELECT a, SUM(b2)/(COUNT(DISTINCT tblC.pk) * COUNT(DISTINCT tblD.pk),
> SUM(c2)/(COUNT(DISTINCT tblB.pk) * COUNT(DISTINCT tblD.pk),
> SUM(d2)/(COUNT(DISTINCT tblB.pk) * COUNT(DISTINCT tblC.pk),
> FROM tblA, tblB, tblC, tblD
> WHERE a = b1 AND a = c1 AND a = d1
> GROUP BY a;

Hm. I seem to have overread the "from three tables" part. You are right. Not pretty. :-)

> >> Inner view solution in that case is:
> >>
> >> select sb2, sc2, sd2, b1 from
> >> (select sum(b2) sb2, b1 from b group by b1),
> >> (select sum(c2) sc2, c1 from c group by c1),
> >> (select sum(d2) sd2, d1 from d group by d1)
> >> where b1 = c1 and c1 = d1
> >
> >Ah, please remind me, in what version of SQL is this allowed?
>
> Oracle7+. If an RDBMS is not supporting inner views, I can still get
> away with views explicitly created in the database like
>
> create view tmpB
> select sum(b2) sb2, b1 from b group by b1

Are you sure? I seem to remember something about views being implemented by rewriting queries that refer to views by substituting the view with its definition. Not directly of course but by replacing the view in the FROM clause with the tables of the FROM clause in the view definition and extending the WHERE clause with the conditions in the WHERE clause of the view definition. If the result wasn't a valid SQL expression then it would not be accepted.

> P.S. I see that my original message looks like a potshot. I apologyse
> for this.

No need. I'm quite trigger-happy myself sometimes. ;-) FWIW I'm not opposed to inner views and agree completely that SQL should have been designed more orthogonal in the first place.

-- 
  Jan Hidders