Mysql order by sum of multiple rows / or nested sum?

I have no clue how to find a fitting title for my problem. I hope it is somehow clear what i mean. I have a query like following:

SELECT
    p.category,
    p.characteristic,
    SUM(p.amount) AS errors
FROM testtable p
WHERE [...]
GROUP BY
    p.characteristic,
    p.category
ORDER BY
    errors DESC,
    p.characteristic ASC

And it returns exactly what I would expect:

CAT2    Characteristic #2   4
CAT3    Characteristic #1   3
CAT2    Characteristic #1   3
CAT2    Characteristic #3   1
CAT3    Characteristic #3   1

But what I’m trying to do is creating some kind of pareto chart with stacked bars, that has the characteristics on its x-axis, the errors on y-axis, and the categories would be stacked up.

however, this result would mean that #2 is first with 4 errors, #1 would be second with 6 errors, which is not correct.

what i need would be a query that sorts the results by the sum of errors by characteristics. is that somehow possible?

Here an example of what i would expect:

CAT3    Characteristic #1   3
CAT2    Characteristic #1   3
CAT2    Characteristic #2   4
CAT2    Characteristic #3   1
CAT3    Characteristic #3   1

#1 is first because the sum of its errors is 6, #2 comes second because the sum is 4 and last would be #3 because the sum is 2

thanks in advance

Answer

Your expected output is easy to come by using analytic functions, assuming you are using MySQL 8+:

SELECT p.category, p.characteristic, SUM(p.amount) AS errors
FROM testtable p
WHERE [...]
GROUP BY p.category, p.characteristic
ORDER BY SUM(SUM(p.amount)) OVER (PARTITION BY p.characteristic) DESC;

On older versions of MySQL, we might have to do more work:

SELECT t1.category, t1.characteristic, t1.errors
FROM
(
    SELECT p.category, p.characteristic, SUM(p.amount) AS errors
    FROM testtable p
    WHERE [...]
    GROUP BY p.category, p.characteristic
) t1
INNER JOIN
(
    SELECT p.characteristic, SUM(p.amount) AS total_errors
    FROM testtable p
    WHERE [...]
    GROUP BY p.characteristic
) t2
    ON t1.characteristic = t2.characteristic
ORDER BY
    t2.total_errors DESC;