Different values ​​are calculated in one 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 are serviceable , under_repair or condemned .

I want to run a query using count to show how many of them can be fixed, how many of them are under repair, how many are convicted of each 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 cannot start an internal query inside the counter.

Here is what I would like the result to look like this:

 item_category total serviceable under repair condemned ============= ===== ============ ============ =========== chair 18 10 5 3 table 12 6 3 3 

Answer 1

You can use the 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 

Answer 2

This is a very simple "group by" query. If you are looking for this, you will find a lot about how it is used.

In your particular case, you want:

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

You will get something like this:

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

Reorder columns if necessary for your purpose

Answer 3

I have a tendency to write this material , so when I forget how to do this, I have a simple example.

The PIVOT offer was new in 11g. Since it was 5+ years ago, I hope you use it.

Data examples

 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 PIVOT request:

 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 the @Ramblin 'Man way to do this (other than using CASE instead of DECODE).

Edit

I just realized that I left the TOTAL column. I'm not sure there is a way to get this column using the PIVOT clause, maybe someone else knows how to do this. It may also be the reason that I do not use it often.