Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Posts
    1

    Unanswered: subtract 2 sql statements

    hi all,

    is it possible to subtract 2 sql statements as in the below example without getting this error.

    SQL> select count(*) from client - select count(*) from episode;
    select count(*) from client - select count(*) from episode
    *
    ERROR at line 1:
    ORA-00933: SQL command not properly ended

    thanks,

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Code:
    SQL> SELECT (SELECT COUNT(*) FROM emp) - (SELECT COUNT(*) FROM dept)
      2  FROM   dual;
    
    (SELECTCOUNT(*)FROMEMP)-(SELECTCOUNT(*)FROMDEPT)
    ------------------------------------------------
                                                  10
    
    1 row selected.
    
    SQL> SELECT e.total - d.total
      2  FROM   ( SELECT COUNT(*) AS total FROM emp ) e
      3       , ( SELECT COUNT(*) AS total FROM dept ) d;
    
    E.TOTAL-D.TOTAL
    ---------------
                 10
    
    1 row selected.
    
    SQL> 
    SQL> WITH e AS ( SELECT COUNT(*) AS total FROM emp )
      2     , d AS ( SELECT COUNT(*) AS total FROM dept )
      3  SELECT e.total - d.total
      4  FROM   e, d;
    
    E.TOTAL-D.TOTAL
    ---------------
                 10
    
    1 row selected.
    
    SQL>

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Select Count(t.time_id) - Count(tz.time_zone_id) From Event.time T, Event.time_zone Tz;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Quote Originally Posted by anacedent
    Select Count(t.time_id) - Count(tz.time_zone_id) From Event.time T, Event.time_zone Tz;
    Watch out with the cartesian product there...
    Code:
    SQL> select * from emp;
    
         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17-DEC-80        800                    20
          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
          7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
          7566 JONES      MANAGER         7839 02-APR-81       2975                    20
          7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
          7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
          7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
          7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
          7839 KING       PRESIDENT            17-NOV-81       5000                    10
          7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
          7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
          7900 JAMES      CLERK           7698 03-DEC-81        950                    30
          7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
          7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
    
    14 rows selected.
    
    SQL> select count( e1.empno ) - count( e2.empno ) from emp e1, emp e2;
    
    COUNT(E1.EMPNO)-COUNT(E2.EMPNO)
    -------------------------------
                                  0
    right, but..
    Code:
    SQL> select count( e1.empno ), count( e2.empno ) from emp e1, emp e2;
    
    COUNT(E1.EMPNO) COUNT(E2.EMPNO)
    --------------- ---------------
                196             196
    .. reveals that is cartesian joining the table. However, my preffered method in that case is..
    Code:
    SQL> select count( distinct e1.rowid ), count( distinct e2.rowid ),
      2         count( distinct e1.rowid ) - count( distinct e2.rowid )
      3    from emp e1, emp e2
      4  /
    
    COUNT(DISTINCTE1.ROWID) COUNT(DISTINCTE2.ROWID) COUNT(DISTINCTE1.ROWID)-COUNT(DISTINCTE2.ROWID)
    ----------------------- ----------------------- -----------------------------------------------
                         14                      14                                               0
    
    SQL>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •