CODEWITHSUNDEEP
sqloracle-database
Puttsche
Puttsche

Reputation: 405

Count the number of rows returned in SQL ORACLE

I have a little problem, my query look like this

select count(A.toto)
from B
inner join C
on B.tata = C.tata
inner join A
on C.tutu = A.tutu
group by A.toto, A.zaza, A.zozo;

and my result look like this :

1
2
1
6
7
4
1
1
1

But I want only the number of rows, for this example, the value that I would like to have is 9. But I don't know how I can have this value... Thank you in advance !!

Upvotes: 1

Views: 348

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You can use count(distinct). Unfortunately, Oracle doesn't support count(distinct) with multiple arguments, so a typical method is just to concatenate the value together:

select count(distinct A.toto || ':' || A.zaza || ':' || A.zozo)
from B inner join
     C
     on B.tata = C.tata inner join
     A
     on C.tutu = A.tutu;

This assumes that. the column values don't have the separator character (or at least in such a way that the concatenation is the same for rows with different key values).

An alternative method is to use a subquery:

select count(*)
from (select 1
      from B inner join
           C
           on B.tata = C.tata inner join
           A
           on C.tutu = A.tutu
      group by A.toto, A.zaza, A.zozo
     ) abc

Upvotes: 2

Related Questions