For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
Hi.
Is there any possible to count rows with condition ?
Generally I want to have something like this:
if field like 'some%' then count1 else count2.
Is there any possibility ??
Hi,
3068783 wrote: Hi. Is there any possible to count rows with condition ? Generally I want to have something like this: if field like 'some%' then count1 else count2. Is there any possibility ??
3068783 wrote:
You can use an expression such as
CASE WHEN field LIKE 'some%' THEN 'Like some%' ELSE 'Unlike some%' END
CASE
WHEN field LIKE 'some%'
THEN 'Like some%'
ELSE 'Unlike some%'
END
Here's an example using the scott.emp table, that counts how many employees have jobs starting with 'SA', and how many don't:
WITH got_grp AS ( SELECT CASE WHEN job LIKE 'SA%' THEN 'Like SA%' ELSE 'Unlike SA%' END AS grp FROM scott.emp -- WHERE ... -- If needed ) SELECT grp , COUNT (*) AS num_found FROM got_grp GROUP BY grp ORDER BY grp ;
WITH got_grp AS
(
SELECT CASE
WHEN job LIKE 'SA%'
THEN 'Like SA%'
ELSE 'Unlike SA%'
END AS grp
FROM scott.emp
-- WHERE ... -- If needed
)
SELECT grp
, COUNT (*) AS num_found
FROM got_grp
GROUP BY grp
ORDER BY grp
;
Output:
GRP NUM_FOUND ---------- --------- Like SA% 4 Unlike SA% 10
GRP NUM_FOUND
---------- ---------
Like SA% 4
Unlike SA% 10
Something like this? (but not very efficient...):
SELECT CASE WHEN FIELD1=cond1 THEN (SELECT COUNT(*) FROM MS038_METERS T2 WHERE T1.FIELD1=T2.FIELD1) AS COUNT_ALL_cond1 ELSE 0 END, CASE WHEN FIELD1=cond2 THEN (SELECT COUNT(*) FROM MS038_METERS T2 WHERE T1.FIELD1=T2.FIELD1) AS COUNT_ALL_cond2 ELSE 0 END --etc FROM TABLE1 T1 GROUP BY FIELD1;
SELECT
CASE WHEN FIELD1=cond1 THEN (SELECT COUNT(*) FROM MS038_METERS T2 WHERE T1.FIELD1=T2.FIELD1) AS COUNT_ALL_cond1
ELSE 0 END,
CASE WHEN FIELD1=cond2 THEN (SELECT COUNT(*) FROM MS038_METERS T2 WHERE T1.FIELD1=T2.FIELD1) AS COUNT_ALL_cond2
ELSE 0 END
--etc
FROM TABLE1 T1
GROUP BY
FIELD1;
You have the full result on each row.
perhaps you're looking for something like this:
-- select sum( case when owner like 'SYS%' then 1 else 0 end ) count_sys, sum( case when owner not like 'SYS%' then 1 else 0 end ) count_others from (select * from dba_tables where rownum < 1000 ); --
--
select sum( case when owner like 'SYS%' then 1 else 0 end ) count_sys,
sum( case when owner not like 'SYS%' then 1 else 0 end ) count_others
from (select * from dba_tables where rownum < 1000 );
regards
Kay
I prefer using NULL values instead of 0. From the result it is the same, but the null value is ignored (skipped ) by the group function, thus we could use count which is clearer from the meaning:
select COUNT( case when owner like 'SYS%' then 1 else NULL end ) count_sys,
COUNT( case when owner not like 'SYS%' then 1 else NULL end ) count_others
from (select * from dba_tables where rownum < 1000 )