For the SQL Gurus out there, a question

From: John Pifer <jp_1872_at_yahoo.com>
Date: 7 Feb 2004 22:04:37 -0800
Message-ID: <56b13b6e.0402072204.543e8f5a@posting.google.com>

I am not good at SQL - hence here goes

Consider this scenario of 2 tables X and Y with a many to many relationship

Table X (name,weightage)
X1 2
X2 1
X3 5
X4 1

Table Y (name ,attrib)
Y1 attrib1
Y2 attrib2
Y3 attrib3
Y4 attrib4

Relationship table Z (id,id)
X1 Y1
X1 Y2
X2 Y1
X3 Y1
X3 Y2
X3 Y4
X4 Y4

So based on Z the distribution (count of Y/ Total Y ) is like Y1 = 100*3/7 Y2=100*2/7 Y3=100*0/7 Y4=100*2/7 Now I want to factor in the weighatge column in X also

So

X1 Y1 2*1
X1 Y2 2*1
X2 Y1 1*1
X3 Y1 5*1
X3 Y2 5*1
X3 Y4 5*1
X4 Y4 1*1

Sum: 21

Given these tables ,How would I factor in the weights and get the distribution in a single SQL query.

Desired Output of SQL Query

Id Attrib Distrib

---

Y1 attrib1 100*8/21
Y2 attrib2 100*7/21
Y3 attrib3 0
Y4 attrib4 100*6/21

Thx
JP Received on Sun Feb 08 2004 - 00:04:37 CST