Oracle Apex Pie Chart SQL Statement

Question

i'm not really much into SQL & Apex, but i need one statement and I would really appreciate your help on this. The syntax of Apex pie charts is this:

SELECT link, label, value

My table looks like these simple sketch:

+------+-----------+---------+
| ID   | Company   | Item    |
+------+-----------+---------+
| 1    | AAA       |Some     |
| 2    | BB        |Stuff    |
| 3    | BB        |Not      |
| 4    | CCCC      |Important|
| 5    | AAA       |For      |
| 6    | DDDDD     |Question?|
+------+-----------+---------+

I want to show the percentage of the companies.

Problem: All companies with less than 5 items should combine to one colum "other". The difficulty for me is to combine the "unimportant" companies.

Until now my statement looks like this:

SELECT null link, 
company label, 
COUNT(ID) value FROM table HAVING COUNT(ID) > 5 GROUP BY company

Here a wonderful diagram-sketch. :D

Thank you for your ideas!

Answer 1

I've not got SQL Developer in front of me but this (or a close variation) should work for you:

WITH company_count
  AS (
      SELECT CASE
                WHEN count(*) < 5
                THEN 'Other'
                ELSE company
              END AS company_name,
             id
        FROM tablename
      ),
      company_group
  AS (
      SELECT company_name,
             count(id) item_count
        FROM company_count
       GROUP BY company_name
     )
SELECT NULL AS link,
       company_name AS label,
       item_count AS value
  FROM company_group;

Hope it helps!