Skip to Main Content

SQL & PL/SQL

Announcement

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!

Counting queries

Christy H.Dec 13 2011 — edited Dec 13 2011
Hello,

I want to count the number of rows that will be displayed the following query
SELECT  STAFF_ID, 
        TRUNC(CVDATE), 
        COUNT(*)
  FROM  cvProperties
  GROUP BY STAFF_ID, 
           TRUNC(CVDATE)
/ 
So far I tried is:
SQL> SELECT  STAFF_ID,
  2          TRUNC(CVDATE),
  3          COUNT(*)
  4    FROM  cvProperties
  5    GROUP BY STAFF_ID,
  6             TRUNC(CVDATE)
  7  /

  STAFF_ID TRUNC(CVD   COUNT(*)
---------- --------- ----------
         1 19-NOV-11        663
         1 27-NOV-11        307
         1 01-DEC-11         60
         1 07-DEC-11         32
         1 28-NOV-11        107
         1 17-NOV-11        368
         1 23-NOV-11          5
         1 29-NOV-11        238
         1 06-DEC-11         91
        41 09-DEC-11          1
         1 08-DEC-11         54

  STAFF_ID TRUNC(CVD   COUNT(*)
---------- --------- ----------
         1 03-DEC-11         79

12 rows selected.

SQL> select (SELECT  STAFF_ID,
  2          TRUNC(CVDATE),
  3          COUNT(*)
  4    FROM  cvProperties
  5    GROUP BY STAFF_ID,
  6             TRUNC(CVDATE)
  7  ) from dual;
select (SELECT  STAFF_ID,
        *
ERROR at line 1:
ORA-00913: too many values


SQL> select count(SELECT  STAFF_ID,
  2          TRUNC(CVDATE),
  3          COUNT(*)
  4    FROM  cvProperties
  5    GROUP BY STAFF_ID,
  6             TRUNC(CVDATE)
  7  ) from dual;
select count(SELECT  STAFF_ID,
             *
ERROR at line 1:
ORA-00936: missing expression
Thanks & best regards
This post has been answered by SomeoneElse on Dec 13 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 10 2012
Added on Dec 13 2011
24 comments
488 views