Oracle - Select Count on a Subquery

Counting subquery results SQL Oracle

You haven't got brackets around the subquery - you have the brackets to indicate the count, but you need an extra set to indicate that it's a subquery.

E.g;

count( (select ....) ) over ...

Moreover, you're reusing the aliases from your outer query in your inner query, plus there's nothing to correlate the subquery to your outer query, so I don't think you're going to get the results you're after.

Additionally, you've labelled a column with an identifier that's over 30 characters, so unless you're on 12.2 with the extended identifiers set, you're going to get ORA-00972: identifier is too long.

Finally, I don't think you need that subquery at all; I think you can just use a conditional count, e.g.:

SELECT DISTINCT ad.state "State",
            COUNT(r.ratingid) over(PARTITION BY ad.state) "Number of Ratings",
            COUNT(DISTINCT CASE WHEN a.awardid IS NOT NULL THEN r.ratingid END) over(PARTITION BY ad.state) "Num Award Winning Movies Rated"
FROM   netflix.addresses ad
JOIN   netflix.ratings100 r
ON     ad.custid = r.custid
JOIN   netflix.movies_awards a
ON     r.movieid = a.movieid
GROUP  BY "State";

You may not even need that distinct; it depends on your data. Hopefully you can play around with that and get it to work for your requirements.

Oracle How to simplify a select count(*) from (subquery)?

An order-by in a subquery isn't useful unless you're filtering the result on rownum (and sometimes will error, depending on context). And you can replace the inner subquery with a join:

SELECT COUNT(*)
FROM (
    SELECT DISTINCT a.col,b.colx,c.coly
    FROM  a
    JOIN b on a.id = b.id
    JOIN c on b.id = c.id
    JOIN cwa on c.id cwa.cid
    WHERE a.xyz = 'something'
    AND   b.hijk = 'something else'
    AND   cwa.csid = 22921
);

You could even do it without a subquery, if you can identify a character that does not appear in any of the three columns you're selecting, so you can use it as a delimiter; e.g. if you'll never have a tilde you could do:

SELECT COUNT(DISTINCT a.col ||'~'|| b.colx ||'~'|| c.coly)
FROM  a
JOIN b on a.id = b.id
JOIN c on b.id = c.id
JOIN cwa on c.id cwa.cid
WHERE a.xyz = 'something'
AND   b.hijk = 'something else'
AND   cwa.csid = 22921;

though whether that's simpler or clearer is a matter of opinion.

---

As count() only takes a single argument, and you want to count the (distinct) combinations of those three columns, this mechanism concatenates all three into a single string and then counts appearances of that string. The delimiter is added so you can distinguish between ambiguous column values, for instance with a contrived example in a CTE:

with cte (col1, col2) as (
  select 'The', 'search' from dual
  union all select 'These', 'arch' from dual
)
select col1, col2,
  col1 || col2 as bad,
  col1 ||'~'|| col2 as good
from cte;

COL1  COL2   BAD         GOOD        
----- ------ ----------- ------------
The   search Thesearch   The~search  
These arch   Thesearch   These~arch  

With simple 'bad' concatenation both rows appear the same; by adding the delimiter to make the 'good' version you can still distinguish between them, so counting distinct concatenate values gets the right answer:

with cte (col1, col2) as (
  select 'The', 'search' from dual
  union all select 'These', 'arch' from dual
)
select count(distinct col1 || col2) as bad_count,
  count (distinct col1 ||'~'|| col2) as good_count
from cte;

 BAD_COUNT GOOD_COUNT
---------- ----------
         1          2

If col1 ended with a tilde, or col2 started with a tilde, you'd be back to ambiguity:

with cte (col1, col2) as (
  select 'The~', 'search' from dual
  union all select 'The', '~search' from dual
)
select col1, col2,
  col1 || col2 as bad,
  col1 ||'~'|| col2 as still_bad
from cte;

COL1 COL2    BAD         STILL_BAD   
---- ------- ----------- ------------
The~ search  The~search  The~~search 
The  ~search The~search  The~~search 

so the delimiter needs to be something you won't find in any values.

ORACLE - Select Count on a Subquery

You can test each column with a regular expression to determine if it is a valid number:

SELECT COUNT(1)
FROM   table_of_ranges
WHERE  CASE WHEN REGEXP_LIKE( RangeA, '^-?\d+(\.\d*)?$' )
            THEN TO_NUMBER( RangeA )
            ELSE NULL END
          < 10
AND    REGEXP_LIKE( RangeB, '^-?\d+(\.\d*)?$' );

Another alternative is to use a user-defined function:

CREATE OR REPLACE FUNCTION test_Number (
  str VARCHAR2
) RETURN NUMBER DETERMINISTIC
AS
  invalid_number EXCEPTION;
  PRAGMA EXCEPTION_INIT(invalid_number, -6502);
BEGIN
  RETURN TO_NUMBER( str );
EXCEPTION
  WHEN invalid_number THEN
    RETURN NULL;
END test_Number;
/

Then you can do:

SELECT COUNT(*)
FROM   table_of_ranges
WHERE  test_number( RangeA ) <= 10
AND    test_number( RangeB ) IS NOT NULL;

Oracle SQL query optimization - getting counts based on a varchar field

If you do not have index on the table2(TYPE) it is deadly to use subquery as you will repeatedly (for each row of TABLE1) perform a FULL TABLE SCAN.

Aparently the Oracle subquery cashing, that could save you, did not kick in.

The function approach will be not much better, except you implement some fucntion result caching on your own.

But there is a simple solution to precalculate the counts in a subquery and join the result to TABLE1.

Note that you calculates the count only once for each type and not for each row of the TABLE1

with cnt as 
(select type, count(*) cnt
from table2 group by type),
cnt2 as 
    (select type, count(*) cnt
    from table3 group by type)
select a.ID, 
    a.FIELD1, 
    a.FIELD2, 
    a.TYPE,
    b.cnt cnt1
    c.cnt cnt2
from  TABLE1 a 
left outer join cnt b
on a.type = b.type
left outer join cnt2 c
on a.type = c.type

You will end with one FTS for each table, aggregation and outer join, which is the minimum you need to do.

SQL subquery COUNT for Oracle

I don't have your tables so I'll try to illustrate it using Scott's sample emp and dept tables:

SQL> select * from dept t1 order by t1.deptno;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select deptno, empno, ename from emp order by deptno;

    DEPTNO      EMPNO ENAME
---------- ---------- ----------
        10       7782 CLARK     --> 3 employees in deptno 10
        10       7839 KING
        10       7934 MILLER
        20       7566 JONES     --> 5 employees in deptno 20
        20       7902 FORD
        20       7876 ADAMS
        20       7369 SMITH
        20       7788 SCOTT
        30       7521 WARD      --> 6 employees in deptno 30
        30       7844 TURNER
        30       7499 ALLEN
        30       7900 JAMES
        30       7698 BLAKE
        30       7654 MARTIN
                                --> 0 employees in deptno 40
14 rows selected.

SQL>

---

A few options you might try:

Correlated subquery:

SQL> select t1.*,
  2         (select count(*) from emp t2 where t2.deptno = t1.deptno) cnt
  3  from dept t1
  4  order by t1.deptno;

    DEPTNO DNAME          LOC                  CNT
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK               3
        20 RESEARCH       DALLAS                 5
        30 SALES          CHICAGO                6
        40 OPERATIONS     BOSTON                 0

SQL>

(Outer) join with the COUNT function and the GROUP BY clause:

SQL> select t1.*, count(t2.rowid) cnt
  2  from dept t1 left join emp t2 on t2.deptno = t1.deptno
  3  group by t1.deptno, t1.dname, t1.loc
  4  order by t1.deptno;

    DEPTNO DNAME          LOC                  CNT
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK               3
        20 RESEARCH       DALLAS                 5
        30 SALES          CHICAGO                6
        40 OPERATIONS     BOSTON                 0

SQL>

(Outer) join with the COUNT function in its analytic form:

SQL> select distinct t1.*,
  2                  count(t2.rowid) over (partition by t1.deptno) cnt
  3  from dept t1 left join emp t2 on t2.deptno = t1.deptno
  4  order by t1.deptno;

    DEPTNO DNAME          LOC                  CNT
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK               3
        20 RESEARCH       DALLAS                 5
        30 SALES          CHICAGO                6
        40 OPERATIONS     BOSTON                 0

SQL>

Oracle SQL COUNT in an EXISTS SELECT

Hmmm . . . use a scalar subquery to calculate the count and compare to "2" in the outer query:

SELECT WO.WONUM, WO.DESCRIPTION, INV.INNUM, INV.STATUSDATE
FROM INV LEFT OUTER JOIN
     WO
     ON INV.WOID = WO.WOID
WHERE (SELECT COUNT(*)
       FROM INVS
       WHERE INVS.INNUM = INV.INNUM AND
             INVS.SITEID = 'ARZ' 
      ) > 2;

Your query is relying on a doubly nested correlation clause which Oracle does not support.

You could also move the subquery to the FROM clause, but this version is more in the spirit of how you have written the query.

Why does the subquery with count(*) return multiple values?

Use conditional aggregation:

SELECT AVG( CASE WHEN a.city = 'London' THEN 1.0 ELSE 0 END)
FROM student s JOIN
     address a
     ON a.address_id = s.id ;

Your query returns an error because subqueries with aggregation queries are tricky.

Having count subquery

Just wrap your second select statement with sub-query :

select avg("JUMLAH") 
from(select d.nama,count(th.fk_distributor) as "JUMLAH"
     from mh_distributor d join 
          th_beli th on th.fk_distributor = d.kode
     group by d.nama
    ) t;

So, your full statement would be :

select d.kode, rpad(d.nama,75,' ') as "NAMA", 
      lpad(count(th.fk_distributor),10,' ') as "JUMLAH"
from mh_distributor d join 
     th_beli th 
     on th.fk_distributor = d.kode
having count(td.fk_produk) > (select avg("JUMLAH") 
                              from(select d.nama,count(th.fk_distributor) as "JUMLAH"
                                   from mh_distributor d join 
                                        th_beli th on th.fk_distributor = d.kode
                                   group by d.nama
                                 ) t
                             )
group by d.nama, td.fk_produk, d.kode
order by d.kode asc;

---

Related Topics