Group by A, B and order by A

tldr: Need to GROUP BY SUM(COST) OVER A,B and also ORDER BY SUM(COST) OVER A

I have a database with columns TYPE, NAME, and COST. My end goal is to create a grouped bar chart in an APEX Application that has TYPE as the X axis, COST as the Y axis, and groups bars by NAME. However, in order to isolate types with the greatest cost, I want to order by the sum of cost over just TYPE so I can select for the top types.

Example Data:

Expected Tabular Result (Order-sensitive):

Here is a similar bar chart to the one I'd like to achieve in Oracle APEX.

Current Work:

SELECT
  TYPE,
  NAME,
  SUM(COST) OVER(PARTITION BY TYPE) AS SUM_COST_BY_TYPE,
  SUM(COST)
FROM
  mytable
GROUP BY
  NAME,
  TYPE
ORDER BY
  SUM_COST_BY_TYPE DESC

FAQ:

To my knowledge, having a grouped bar chart in Oracle APEX requires that you have a GROUP BY in the SQL query, so I believe using two PARTITION BYs will not work without some extra steps.

I am aware of stacked charts in APEX, they are not an option.

You can GROUP BY and SUM aggregation function to get the cost in each name/type group and then use the SUM analytic function to get the total cost per type and then use the DENSE_RANK analytic function to generate the rankings:

SELECT type,
       name,
       DENSE_RANK() OVER (ORDER BY type_cost DESC, type) AS type_rank,
       total_cost
FROM   (
  SELECT type,
         name,
         SUM(SUM(cost)) OVER (PARTITION BY type) AS type_cost,
         SUM(cost) AS total_cost
  FROM   mytable
  GROUP BY
        type,
        name
)
ORDER BY
      type_rank,
      name

Which, for the sample data:

CREATE TABLE mytable (type, name, cost) AS
SELECT 'bad',       'A',  500 FROM DUAL UNION ALL
SELECT 'good',      'A',  300 FROM DUAL UNION ALL
SELECT 'bad',       'A', 1000 FROM DUAL UNION ALL
SELECT 'very bad',  'B',  200 FROM DUAL UNION ALL
SELECT 'very good', 'B', 2300 FROM DUAL UNION ALL
SELECT 'good',      'C',  400 FROM DUAL UNION ALL
SELECT 'bad',       'A',  100 FROM DUAL UNION ALL
SELECT 'very good', 'C',  700 FROM DUAL UNION ALL
SELECT 'good',      'A',  800 FROM DUAL UNION ALL
SELECT 'bad',       'A',  400 FROM DUAL UNION ALL
SELECT 'very good', 'C',  500 FROM DUAL UNION ALL
SELECT 'good',      'B',  300 FROM DUAL UNION ALL
SELECT 'bad',       'C',  350 FROM DUAL UNION ALL
SELECT 'bad',       'C',  600 FROM DUAL UNION ALL
SELECT 'good',      'B',  440 FROM DUAL UNION ALL
SELECT 'bad',       'B',  502 FROM DUAL UNION ALL
SELECT 'bad',       'A',  300 FROM DUAL UNION ALL
SELECT 'good',      'A',  456 FROM DUAL UNION ALL
SELECT 'bad',       'B',  340 FROM DUAL;

Outputs:

fiddle