同一列上的不同值计数 [英] Different value counts on same column

问题描述

我是Oracle新手.我有一个包含三列的Oracle表:serialno，item_category和item_status.在第三列中，行的值为serviceable，under_repair或condemned.

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.

我想使用count运行查询以显示有多少是可维修的，有多少正在维修中，有多少针对每个商品类别而定.

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.

我想运行以下内容:

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 

推荐答案

您可以在COUNT函数中使用CASE或DECODE语句.

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;

输出:

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

这篇关于同一列上的不同值计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！