CODEWITHSUNDEEP
sqloracle-databasecount
Marc
Marc

Reputation: 352

Oracle - Different count clause on the same line

I wish I could find a request allowing me to have on the same result line, 2 values obtained with a different clause:

For example, let's say that I have this table:

ID   |VAL
----------
0    |    1
1    |    0
2    |    0
3    |    1
4    |    0
5    |    0

I wish I could, in the same request, select the number of lines having val = 1, the number of total lines, (and if possible the total percentage of one count on the other) which would give result set like this:

nb_lines | nb_val_1 | ratio
---------------------------
6        |     2    |   0.5

I tried something like:

select count(t1.ID), (select count t2.ID
                     from table t2 where t2.val = 1
                     )
FROM table t1

But obviously, this syntax doesn't exist (and it wouldn't give me the ratio). How could I perform this request ?

Upvotes: 0

Views: 132

Answers (1)

Noel
Noel

Reputation: 10525

Try this query which uses CASE to count only those rows we need.

SELECT nb_lines,nb_val_1,nb_val_0, nb_val_1/nb_val_0 FROM
 (SELECT COUNT (t1.ID) nb_lines, 
        COUNT (CASE
                WHEN t1.val = 1
                  THEN 1
                ELSE NULL
               END) nb_val_1, 
        COUNT (CASE
                WHEN t1.val = 0
                  THEN 1
                ELSE NULL
               END) nb_val_0
   FROM tabless t1);

Upvotes: 2

Related Questions