Finding the 2 closest corresponding rows to the average of the sum of multiplied values, grouped by-CodePudding

I want to find the average values of a sum of multiplied values grouped by a matching title, in order to give them a corresponding rating, and then find the 2 closest to the input value.

SELECT titleValueAVG / 3 AS average,
       title
  FROM (
           SELECT Sum(a) AS titleValueAVG,
                  title
             FROM (
                      SELECT value * 1 AS a,
                             title
                        FROM Table1
                       WHERE type = 'A' AND 
                             contesting = 'yes'
                      UNION ALL
                      SELECT value * 2,
                             title
                        FROM Table1
                       WHERE type = 'A' AND 
                             contesting = 'no'
                      UNION ALL
                      SELECT value * 3,
                             title
                        FROM Table1
                       WHERE type = 'A' AND 
                             contesting = 'undecided'
                  )
            GROUP BY title
            ORDER BY title = 'Australia' DESC,
                     ABS(titleValueAVG) - (
                                        SELECT value * 1 AS a,
                                               title
                                          FROM Table1
                                         WHERE type = 'A' AND 
                                               contesting = 'yes' AND title = 'Australia'
                                        UNION ALL
                                        SELECT value * 2,
                                               title
                                          FROM Table1
                                         WHERE type = 'A' AND 
                                               contesting = 'no' AND title = 'Australia'
                                        UNION ALL
                                        SELECT value * 3,
                                               title
                                          FROM Table1
                                         WHERE type = 'A' AND 
                                               contesting = 'undecided' AND title = 'Australia'
                                    )
       ) limit 2;

From an example table:

| Title    | Type         | Competing      | Value          |

| -------- | -------------| -------------- | -------------- |

| Australia| A            | yes            | 26             |

| Australia| A            | no             | 162            |

| Australia| A            | undecided      | 37             |

| Spain    | A            | yes            | 14             |

| Spain    | A            | no             | 101            |

| Spain    | A            | undecided      | 11             |

| Ireland  | A            | yes            | 124            |

| Ireland  | A            | no             | 62             |

| Ireland  | A            | undecided      | 9              |

| Nigeria  | C            | yes            | 4              |

| Nigeria  | C            | no             | 11             |

| Nigeria  | C            | undecided      | 7              |

| Colombia | A            | yes            | 26             |

| Colombia | A            | no             | 12             |

| Colombia | A            | undecided      | 19             |

| Turkey   | A            | yes            | 29             |

| Turkey   | A            | no             | 145            |

| Turkey   | A            | undecided      | 24             |

| Malta    | B            | yes            | 1              |

| Malta    | B            | no             | 11             |

| Malta    | B            | undecided      | 4              |

| Mexico   | A            | yes            | 74             |

| Mexico   | A            | no             | 19             |

| Mexico   | A            | undecided      | 12             |

| Slovenia | B            | yes            | 16             |

| Slovenia | B            | no             | 22             |

| Slovenia | B            | undecided      | 11             |

| Canada   | A            | yes            | 29             |

| Canada   | A            | no             | 164            |

| Canada   | A            | undecided      | 40             |

| Kenya    | C            | yes            | 8              |

| Kenya    | C            | no             | 12             |

| Kenya    | C            | undecided      | 0              |

So, in this example, I would like to return from an input title 'Australia:

| Title    | average        |

| -------- | -------------- |

| Australia| 154            |

| Canada   | 159            |

| Turkey   | 130            |

My attempted solution is above, I've tried multiple ways to organise the order by, which is what I think is the issue, but I can't get it to work at all. If anyone could help me fix this I'd really appreciate it.

CodePudding user response：

Use conditional aggregation:

WITH cte AS (
  SELECT title,
         ROUND(SUM(value *
             CASE competing
               WHEN 'yes' THEN 1
               WHEN 'no' THEN 2
               WHEN 'undecided' THEN 3
             END
         ) / 3.0) average
  FROM tablename
  WHERE type = 'A'
  GROUP BY title
)
SELECT *
FROM cte
ORDER BY title = 'Australia' DESC,
         ABS(average - (SELECT average FROM cte WHERE title = 'Australia'))
LIMIT 3;

See the demo.