SQL select计数1个值

Question

我有一个这样的表:

+---------+------------+--------+--------------+
|   Id    |    Name    | Status | Content_type |
+---------+------------+--------+--------------+
| 2960671 | PostJob    | Error  | general_url  |
| 2960670 | auto_index | Done   | general_url  |
| 2960669 | auto_index | Done   | document     |
| 2960668 | auto_index | Error  | document     |
| 2960667 | auto_index | Error  | document     |
+---------+------------+--------+--------------+

我想计算每种类型中状态为"错误"的数量，因此在结果中它将是1x general_url和2x document

我试过这样做:

SELECT COUNT(DISTINCT Content_type) from Indexing where Status = 'Error';

但是我不知道怎么把content_type弄出来

Answer 1

你想要这个

select Content_type, 
       count(Status) 
from Indexing 
where Status='Error' 
group by Content_type;

Answer 2

GROUP BY应该做这项工作:

SELECT Content_type, COUNT(Id) from Indexing where Status = 'Error' GROUP BY Content_type;

解释:

COUNT (x)计算组中的行数，COUNT (*)也会这样做。

COUNT (DISTINCT x)计算组中不同值的个数。

如果没有GROUP BY子句，组是整个记录集，因此在您的示例中，您将看到单个值(2)作为您的结果;例如，在集合中有两个不同的Content_types。

Answer 3

SQL Fiddle Oracle

<<p> 模式/strong>

create table test
(id varchar2(10),
name varchar2(30),
status varchar2(20),
content_type varchar2(30)
);
 insert into test values('2960671','PostJob','Error','general_url');
 insert into test values('2960670','auto_index','Done','general_url');
 insert into test values('2960669','auto_index','Done','document');
 insert into test values('2960668','auto_index','Error','document');
 insert into test values('2960667','auto_index','Error','document');

Select查询

SELECT  LISTAGG(content_type, ',') WITHIN GROUP (ORDER BY rownum) AS content_type,
count(content_type) as content_type_count
from
(
select distinct(content_type) content_type
FROM   test
where status='Error'
);

|         CONTENT_TYPE | CONTENT_TYPE_COUNT |
|----------------------|--------------------|
| document,general_url |                  2 |

这里的想法是打印逗号分隔的content_type值这样你就可以知道content_type的计数以及实际值

Answer 4

试试这个

SELECT count(`content_type`) as 'count', content_type as 'x content type' FROM `tablename` where status= 'Error' group by(`content_type`)