Different value counts on same column

oracle, pivot-table, sql

Solution

You can either use CASE or DECODE statement inside the COUNT function.

  SELECT item_category,
         COUNT (*) total,
         COUNT (DECODE (item_status, 'serviceable', 1)) AS serviceable,
         COUNT (DECODE (item_status, 'under_repair', 1)) AS under_repair,
         COUNT (DECODE (item_status, 'condemned', 1)) AS condemned
    FROM mytable
GROUP BY item_category;

Output:

ITEM_CATEGORY   TOTAL   SERVICEABLE UNDER_REPAIR    CONDEMNED
----------------------------------------------------------------
chair           5       1           2               2
table           5       3           1               1

Problem

I am new to Oracle. I have an Oracle table with three columns: `serialno`, `item_category` and `item_status`. In the third column the rows have values of `serviceable`, `under_repair` or `condemned`. I want to run the query using count to show how many are serviceable, how many are under repair, how many are condemned against each item category. I would like to run something like: ``` select item_category , count(......) "total" , count (.....) "serviceable" , count(.....)"under_repair" , count(....) "condemned" from my_table group by item_category ...... ``` I am unable to run the inner query inside the count. Here's what I'd like the result set to look like: ``` item_category total serviceable under repair condemned ============= ===== ============ ============ =========== chair 18 10 5 3 table 12 6 3 3 ```

Original source