Stuck with MySQL and GROUP BY

Question

I have a table with some statistics. Example:

date userId attempts good bad
2010-08-23 1 5 4 1
2010-08-23 2 10 6 4
2010-08-23 3 6 3 3
2010-08-23 4 8 2 6

Each user must do something, and the result is either good or bad. I would like to know what is the relative score for each user, compared to other users that day. Example:

User 1 made 5 attempts, and 4 of them were good. Thus, his attempts were good. For other users that day it was 60%, 50% and 25%. Thus, the relative score of successful attempts by user 1 on this day is 80 / (80 + 60 + 50 + 25) ≈ 37%.

But I am stuck at this point:

SELECT
  date,
  userId,
  ( (good / attempts) / x ) * 100 AS score_good
  ( (bad / attempts) / y ) * 100 AS score_bad
FROM stats
GROUP BY date, userId -- ?

x - (good / attempts) , y - (bad / attempts) . ?

, , ,

date        userId  score_good
2010-08-23  1       37%
2010-08-23  2       28% (60 / (80 + 60 + 50 + 25))
etc

:

userId   score_good_total
1        ...

score_good_total score_good, .

x y , , , , , , , , , .

Answer 1

SQL-fu, .

// this would be the working query
SELECT 
   *,
   @score := good / attempts * 100 AS score,
   @t_score := (SELECT SUM(good / attempts * 100) FROM stats) as t_score ,
   @score / @t_score as relative_score_good
FROM stats

, .

- , uncorrelated scalar subquery , , , ( EXPLAIN, , .

, ( !): user defined variables, @variable.

---

( , SQL ).

// create the demo table
CREATE TABLE `test`.`stats` (
   `date` DATE NOT NULL ,
   `id` INT NOT NULL ,
   `attempts` INT NOT NULL ,
   `good` INT NOT NULL ,
   `bad` INT NOT NULL ,
   INDEX ( `id` , `attempts` , `good` , `bad` ) 
) ENGINE = MYISAM

// inject some values
INSERT INTO `test`.`stats` (`date`,`id`,`attempts` ,`good` ,`bad`)
VALUES 
   ('2010-08-23', '1', '5', '4', '1'), 
   ('2010-08-23', '2', '10', '6', '4'), 
   ('2010-08-23', '3', '6', '3', '3'), 
   ('2010-08-23', '4', '8', '2', '6');

, ! , , - . 4 , !;)

Answer 2

, , . , , , .

, , " " , , . , , ; " ".