Multiple count(*) in single query

Question

I need a single query to fetch the counts based on some condition(oracle).

select count(*) AS TOTAL_ROWS from table

select count(*) AS TOTAL_ERROR_ROWS from table where ERROR_CODE is not null

select count(*) AS TOTAL_SUCCESS_ROWS from table where ERROR_CODE is  null

I want to fetch all three results in 1 query. I have tried like below after googling but it is not working:

select
   count(*) as TotalCount,
   count(case when { where ERROR_CODE is not null} then 1 else 0 end) as QualifiedCount
from
   wli_qs_report_attribute

select count(*) as TOTAL,
    count(case when ERROR_CODE is not null then 1 else 0 end) as ExecCount,
from wli_qs_report_attribute

it doesn't work.

Lennart - Slava Ukraini · Accepted Answer · 2014-05-08 06:43:02Z

2

SELECT TOTAL_ROWS, TOTAL_ERROR_ROWS, TOTAL_ROWS-TOTAL_ERROR_ROWS AS TOTAL_SUCCESS_ROWS FROM ( SELECT COUNT(*) AS TOTAL_ROWS , COUNT(ERROR_CODE) AS TOTAL_ERROR_ROWS FROM table ) AS T

answered May 8, 2014 at 6:43

Lennart - Slava Ukraini

7,2111 gold badge23 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

souslov · Accepted Answer · 2014-05-08 05:49:03Z

1

You can use UNION :

select count(*) AS TOTAL_ROWS from table

UNION

select count(*) AS TOTAL_ROWS from table where ERROR_CODE is not null

UNION

select count(*) AS TOTAL_ROWS from table where ERROR_CODE is  null

But it will display all three COUNTs in one column. To display it in three different columns try the following :

SELECT COUNT(*) AS TOTAL_ROWS
     , COUNT(CASE WHEN ERROR_CODE IS NOT NULL THEN * END) AS TOTAL_ERROR_ROWS
     , COUNT(CASE WHEN ERROR_CODE IS NULL THEN * END) AS TOTAL_SUCCESS_ROWS
FROM table

edited May 8, 2014 at 5:49

answered May 8, 2014 at 5:43

souslov

44.7k11 gold badges92 silver badges113 bronze badges

Comments

Kaleel · Accepted Answer · 2014-05-08 06:14:35Z

1

SELECT COUNT(*) AS TOTAL_ROWS,
       SUM(CASE WHEN ERROR_CODE IS NOT NULL THEN 1 end) AS TOTAL_ERROR_ROWS,
       SUM(CASE WHEN ERROR_CODE IS NULL THEN 1 END) AS TOTAL_SUCCESS_ROWS 
FROM table

edited May 8, 2014 at 6:14

user330315

answered May 8, 2014 at 5:50

Kaleel

3211 silver badge6 bronze badges

Comments

user2338816 · Accepted Answer · 2014-05-08 05:43:09Z

0

Instead of:

count(case when ERROR_CODE is not null then 1 else 0 end) as ExecCount

Try:

sum(case when ERROR_CODE is not null then 1 else 0 end) as ExecCount

answered May 8, 2014 at 5:43

user2338816

2,17311 silver badges11 bronze badges

Collectives™ on Stack Overflow

Multiple count(*) in single query

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related