MySQL how do I correct this subquery?

Question

I have the following table:

select * from points
+---------+-------------------+------+------+
| NAME    | TITLE             | Type | RANK |
+---------+-------------------+------+------+
| A       | Hippo             | H    |  1   |
| A       | Hippo             | M    |  1   |
| A       | Hippo             | H    | N/A  |
| A       | Hippo             | H    |  1   |
| A       | Hippo             | H    | N/A  |
| B       | Snail             | H    |  1   |
| B       | Snail             | M    |  1   |
| B       | Snail             | L    |  1   |
| C       | Dog               | H    |  1   |
| C       | Dog               | M    |  1   |
+---------+-------------------+------+------+

Desired output

+---------+----------+-------+
| NAME    | TITLE    | SCORE |
+---------+----------+-------+
| A       | Hippo    |   60  | <--[(2xH)=40 + (1xM)=20] =60
| B       | Snail    |  100  | <--[(1xH)=70 + (1xM)=20 + (1xL)=10] =100
| C       | Dog      |  100  | <--This should happen because [(1xH)=80 + (1xM)=20] =100
+---------+----------+-------+

Computations required:

• Type can have only three values: {H, M, L};

• When all values are present, they are graded as followed:

  H=70 M=20 L=10

• If an name has more than one kind of Type (H, M, or L) then points are distributed as followed:

• H/(number of H) ; M/(number of M); L/(number of L) = 100

-- Example: A has 4 H therefore 70 / 4 = 17.5 for each H

But some names have a complete set with out having all 'Types. -- example : C has Type values: 'H&M` only

• CASE H&M

H=80 M=20

• CASE M&L

M=60 L=40

• CASE H&L

H=90 L=10

And also

• if only H is presnet H=100

• if only M is presnet M=100

• if only L is presnet L=100

Answer 1

If I understand correctly, this is what you want:

SELECT name,
       title,
       CAST(
       (      -- only have H, or only have M, or only have L:
         CASE WHEN  `# of H` = 0  AND  `# of M` = 0  THEN  100 * `# of active L` / `# of L`
              WHEN  `# of H` = 0  AND  `# of L` = 0  THEN  100 * `# of active M` / `# of M`
              WHEN  `# of M` = 0  AND  `# of L` = 0  THEN  100 * `# of active H` / `# of H`
              -- only have H & M, or only have H & L, or only have M & L:
              WHEN  `# of H` = 0  THEN  60 * `# of active M` / `# of M` + 40 * `# of active L` / `# of L`
              WHEN  `# of M` = 0  THEN  0  -- ??????????
              WHEN  `# of L` = 0  THEN  80 * `# of active H` / `# of H` + 20 * `# of active M` / `# of M`
              -- have all three:
              ELSE  70 * `# of active H` / `# of H` + 20 * `# of active M` / `# of M` + 10 * `# of active L` / `# of L`
         END
       ) AS SIGNED ) AS score
  FROM ( SELECT name,
                title,
                SUM(IF(         type = 'H', 1, 0))  AS `# of H`,
                SUM(IF(rank AND type = 'H', 1, 0))  AS `# of active H`,
                SUM(IF(         type = 'M', 1, 0))  AS `# of M`,
                SUM(IF(rank AND type = 'M', 1, 0))  AS `# of active M`,
                SUM(IF(         type = 'L', 1, 0))  AS `# of L`,
                SUM(IF(rank AND type = 'L', 1, 0))  AS `# of active L`
           FROM points
          GROUP
             BY name,
                title
       ) t
 ORDER
    BY name
;