Different value counts on same column

Question

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 

Solution 1

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

Solution 2

This is a very basic "group by" query. If you search for that you will find plenty of documentation on how it is used.

For your specific case, you want:

select item_category, item_status, count(*)
  from <your table>
 group by item_category, item_status;

You'll get something like this:

item_category   item_status   count(*)
======================================
Chair           under_repair  7
Chair           condemned     16
Table           under_repair  3

Change the column ordering as needed for your purpose

Solution 3

I have a tendency of writing this stuff up so when I forget how to do it, I have an easy to find example.

The PIVOT clause was new in 11g. Since that was 5+ years ago, I'm hoping you are using it.

Sample Data

create table t
(
  serialno number(2,0),
  item_category varchar2(30),
  item_status varchar2(20)
);

insert into t ( serialno, item_category, item_status )
select 
  rownum serialno,
  ( case 
      when rownum <= 12 then 'table'
      else 'chair'
    end ) item_category,
  ( case
      --table status
      when rownum <= 12 
        and rownum <= 6 
      then 'servicable'
      when rownum <= 12
        and rownum between 7 and 9 
      then 'under_repair'
      when rownum <= 12
        and rownum > 9 
      then 'condemned'
      --chair status
      when rownum > 12
        and rownum < 13 + 10 
      then 'servicable'
      when rownum > 12
        and rownum between 23 and 27
      then 'under_repair'
      when rownum > 12
        and rownum > 27
      then 'condemned'
    end ) item_status
from 
  dual connect by level <= 30;
commit;

and the PIVOT query:

select *
from
  (
    select
      item_status stat,
      item_category,
      item_status
    from t
  )
pivot
(
  count( item_status )
  for stat in ( 'servicable' as "servicable", 'under_repair' as "under_repair", 'condemned' as "condemned" )
);

ITEM_CATEGORY servicable under_repair  condemned
------------- ---------- ------------ ----------
chair                 10            5          3 
table                  6            3          3 

I still prefer @Ramblin' Man's way of doing it (except using CASE in place of DECODE) though.

Edit

Just realized I left out the TOTAL column. I'm not sure there's a way to get that column using the PIVOT clause, perhaps someone else knows how. May also be the reason I don't use it that often.

Comments

[Jacob]

Yes your approach is a better solution.

[Burhan Ali]

Since I wrote this answer, the question was edited with some example output. This answer is no longer the most suitable one. The one by @Ramblin' Man looks better.

[Noel]

Nice. By the way, if you click on the check mark beside the answer, you'll get 2 points. :)