Re: For the SQL Gurus out there, a question

Oracle or SQLServer? This was tested on SQLServer 2000 but is ANSI-92 so I think it should be OK on Oracle 9.

CREATE TABLE TableX (xcol CHAR(2) PRIMARY KEY, weightage INTEGER NOT NULL)

INSERT INTO TableX (xcol, weightage) VALUES ('X1',2)
INSERT INTO TableX (xcol, weightage) VALUES ('X2',1)
INSERT INTO TableX (xcol, weightage) VALUES ('X3',5)
INSERT INTO TableX (xcol, weightage) VALUES ('X4',1)

CREATE TABLE TableY (ycol CHAR(2) PRIMARY KEY, attrib VARCHAR(7) NOT NULL)

INSERT INTO TableY (ycol, attrib) VALUES ('Y1','attrib1')
INSERT INTO TableY (ycol, attrib) VALUES ('Y2','attrib2')
INSERT INTO TableY (ycol, attrib) VALUES ('Y3','attrib3')
INSERT INTO TableY (ycol, attrib) VALUES ('Y4','attrib4')

CREATE TABLE TableZ (xcol CHAR(2) REFERENCES TableX (xcol), ycol CHAR(2) REFERENCES TableY (ycol), PRIMARY KEY (xcol,ycol))

INSERT INTO TableZ (xcol, ycol) VALUES ('X1','Y1')
INSERT INTO TableZ (xcol, ycol) VALUES ('X1','Y2')
INSERT INTO TableZ (xcol, ycol) VALUES ('X2','Y1')
INSERT INTO TableZ (xcol, ycol) VALUES ('X3','Y1')
INSERT INTO TableZ (xcol, ycol) VALUES ('X3','Y2')
INSERT INTO TableZ (xcol, ycol) VALUES ('X3','Y4')
INSERT INTO TableZ (xcol, ycol) VALUES ('X4','Y4')

SELECT Y.ycol, Y.attrib,
 COALESCE(100*SUM(X.weightage)/
  (SELECT CAST(SUM(X.weightage) AS REAL)    FROM TableX AS X
   JOIN TableZ AS Z
    ON X.xcol = Z.xcol),0)
 FROM TableY AS Y
 LEFT JOIN TableZ AS Z
  ON Y.ycol = Z.ycol
 LEFT JOIN TableX AS X
  ON Z.xcol = X.xcol
 GROUP BY Y.ycol, Y.attrib

--

David Portas
SQL Server MVP
--
Received on Sun Feb 08 2004 - 02:11:17 CST