Thanks much that helped - Sorry for the bad names :)
joe.celko_at_northface.edu (--CELKO--) wrote in message news:<a264e7ea.0402101535.4f7b8af5_at_posting.google.com>...
> I really hate you table names which are almost impossible to
> understand (blah, blah, sample data -- but let's make life easier for
> people anyway). How about weighted scores in various contests as the
> human-friendly version?
>
> The lack of DDL is also a pain, since we have to make all kinds of
> assumptions about NULLs and keys. But is this what you wanted to say?
>
> CREATE TABLE Scores
> (score_type CHAR(2) NOT NULL PRIMARY KEY,
> wgt INTEGER NOT NULL CHECK (wgt > 0));
>
> INSERT INTO Scores VALUES ('X1', 2);
> INSERT INTO Scores VALUES ('X2', 1);
> INSERT INTO Scores VALUES ('X3', 5);
> INSERT INTO Scores VALUES ('X4', 1);
>
> CREATE TABLE Contests
> (contest_id CHAR(2) NOT NULL PRIMARY KEY,
> contest_name CHAR(10) NOT NULL);
>
> INSERT INTO Contests VALUES ('Y1', 'Contest-1');
> INSERT INTO Contests VALUES ('Y2', 'Contest-2');
> INSERT INTO Contests VALUES ('Y3', 'Contest-3');
> INSERT INTO Contests VALUES ('Y4', 'Contest-4');
>
> CREATE TABLE Standings
> (score_type CHAR(2) NOT NULL REFERENCES Scores (score_type),
> contest_id CHAR(2) NOT NULL REFERENCES Contests (contest_id),
> PRIMARY KEY (score_type, contest_id));
>
> INSERT INTO Standings VALUES ('X1', 'Y1');
> INSERT INTO Standings VALUES ('X1', 'Y2');
> INSERT INTO Standings VALUES ('X2', 'Y1');
> INSERT INTO Standings VALUES ('X3', 'Y1');
> INSERT INTO Standings VALUES ('X3', 'Y2');
> INSERT INTO Standings VALUES ('X3', 'Y4');
> INSERT INTO Standings VALUES ('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 <<
>
> I am feeling pedantic, so let's do this in steps to show the reasoning
> behind each part and then assemble the final query from them.
>
> SELECT C1.contest_id, (1.0 * COUNT(s1.contest_id)/(SELECT 1.0 *
> COUNT(*) FROM Standings)) AS distribution
> FROM Contests AS C1
> LEFT OUTER JOIN
> Standings AS ST1
> ON C1.contest_id = ST1.contest_id
> GROUP BY C1.contest_id;
>
> The 1.0 is to avoid integer math; it could have been a CAST()
>
> >> Now I want to factor in the wgt column in Scores also <<
>
> Here is where I get confused; you show a constant "wgt * 1" for each
> row of the standings table, but if this table is all key, isn't the
> one redundant? On that assumption:
>
> SELECT ST1.score_type, ST1.contest_id, S1.wgt
> FROM Scores AS S1,
> Standings AS ST1
> WHERE ST1.score_type = S1.score_type;
>
> >> Sum: 21 <<
>
> SELECT SUM(S1.wgt)
> FROM Scores AS S1,
> Standings AS ST1
> WHERE ST1.score_type = S1.score_type;
>
> >> Given these tables, How would I factor in the weights and get the
> distribution in a single SQL query. <<
>
> SELECT C1.contest_id, C1.contest_name,
> SUM(S1.wgt)/
> (1.0 * (SELECT SUM(S1.wgt)
> FROM Scores AS S1, Standings AS ST1
> WHERE ST1.score_type = S1.score_type)) AS wgt-distrib
> FROM (Contests AS C1
> LEFT OUTER JOIN
> Standings AS ST1
> ON ST1.contest_id = C1.contest_id)
> LEFT OUTER JOIN
> Scores AS S1
> ON ST1.score_type = S1.score_type
> GROUP BY C1.contest_id, C1.contest_name;
>
> The optimizer ought to pick out the scalar subquery and do it once.
> The big worry is preserving the Contests table via nested LEFT UTER
> JOINs.
>
> Results
> contest_id contest_name wgt-distrib
> =================================
> Y1 Contest-1 .38095238095238
> Y2 Contest-2 .33333333333333
> Y3 Contest-3 NULL
> Y4 Contest-4 .28571428571428
>
> Notice I left a NULL in the answer. This means that **nobody** got a
> score in contest 'Y3', while a zero implies that **somebody** got a
> score of zero in contest 'Y3' -- big difference.
>
> Does it help to explain thigns to this detail or do you need more?
Received on Sun Feb 15 2004 - 22:16:07 CST
