CODEWITHSUNDEEP
sql
Kiwi
Kiwi

Reputation: 2773

SQL select counts on 1 value

I have a table like this:

+---------+------------+--------+--------------+
|   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     |
+---------+------------+--------+--------------+

And I want to count how many of each type that has 'Error' as status, so in the result it would be 1x general_url and 2x document

I tried something like this:

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

But I could not figure out how to get the content_type out of it

Upvotes: 2

Views: 98

Answers (4)

DigitalDan
DigitalDan

Reputation: 2637

GROUP BY should do the job:

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

Explanation:

COUNT (x) counts the number of rows in the group, COUNT (*) would do the same.

COUNT (DISTINCT x) counts the number of distinct values in the group.

Without a GROUP BY clause the group is the whole set of records, so in your example you would have seen a single value (2) as your result; i.e. there are 2 distinct Content_types in the set.

Upvotes: 1

viral Pathak
viral Pathak

Reputation: 109

Try this One 

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

Upvotes: 0

Maulik Shah
Maulik Shah

Reputation: 402

SQL Fiddle Oracle

Schema

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 Query

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'
);

Output

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

The idea here is to print comma separated content_type values so that you can know the count of content_type along with actual values

Upvotes: 0

dinwal
dinwal

Reputation: 467

You want this

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

Upvotes: 4

Related Questions