Re: Simple SQL?

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;

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

My syntax is nothing more than a shorthand then.

P.S. I see that my original message looks like a potshot. I apologyse for this. Received on Sun Jul 22 2001 - 01:25:21 CEST