Reputation: 6346
CREATE TABLE TEMP25
(
STATUS VARCHAR2(200 BYTE)
);
Insert into TEMP25
(STATUS)
Values
('PENDING');
Insert into TEMP25
(STATUS)
Values
('ERROR');
Insert into TEMP25
(STATUS)
Values
('ERROR');
Insert into TEMP25
(STATUS)
Values
('ERROR');
Insert into TEMP25
(STATUS)
Values
('NOT_REQUIRED');
Insert into TEMP25
(STATUS)
Values
('NOT_REQUIRED');
Insert into TEMP25
(STATUS)
Values
('PENDING');
Insert into TEMP25
(STATUS)
Values
('PENDING');
Insert into TEMP25
(STATUS)
Values
('ERROR');
Insert into TEMP25
(STATUS)
Values
('NOT_REQUIRED');
Insert into TEMP25
(STATUS)
Values
('ERROR');
Insert into TEMP25
(STATUS)
Values
('INVALID');
Insert into TEMP25
(STATUS)
Values
('INVALID');
Insert into TEMP25
(STATUS)
Values
('PENDING');
I want to write a query such that i get count like
TOTAL_RECORDS TOTAL_PENDING TOTAL_NOT_REQUIRED TOTAL_PENDING
-----------------------------------------------------------------
14 4 3 5
-----------------------------------------------------------------
I want the record in sequence and in one query , i ahve tried with with table
clause it works ...is there any other solution ...and dnt want invalid records
Upvotes: 1
Views: 103
Reputation: 52675
You can use SUM/CASE
or (SUM/DECODE
if you prefer)
Select
COUNT(*) TOTAL_RECORDS ,
SUM(case when status = 'PENDING' then 1 else 0 END) TOTAL_PENDING,
SUM(case when status = 'NOT_REQUIRED' then 1 else 0 END) TOTAL_NOT_REQUIRED,
SUM(case when status = 'ERROR' then 1 else 0 END) TOTAL_ERROR
FROM temp25
You can also use pivot but getting the count(*) is a little ugly
WITH
COUNTS AS(
select *
from (
select status
from TEMP25 t
)
pivot
(
count(status)
for status in ('PENDING' AS TOTAL_PENDING,
'NOT_REQUIRED' AS TOTAL_NOT_REQUIRED,
'ERROR' AS TOTAL_ERROR)
))
SELECT COUNT(*) total_records,
total_pending,
total_not_required,
total_error
FROM temp25,
counts
GROUP BY total_pending,
total_not_required,
total_error
Upvotes: 5
Reputation: 2524
In Oracle you can
SELECT
(SELECT COUNT(*) FROM TEMP25) total,
(SELECT COUNT(*) FROM TEMP25 WHERE STATUS='PENDING') pending,
[...]
FROM dual ;
All databases have a null table you can use from this kind of thing.
Upvotes: 0
Reputation: 176956
You can easily make use of case when statement and get this result ...have look to it also...
Select COUNT(*) TOTAL_RECORDS ,
SUM(case when status = 'ERROR' then 1 else 0 END) TOTAL_ERROR
SUM(case when status = 'NOT_REQUIRED' then 1 else 0 END) TOTAL_NOT_REQUIRED,
SUM(case when status = 'PENDING' then 1 else 0 END) TOTAL_PENDING,
FROM temp25
Upvotes: 4