Re: SQL Brain Teaser

From: Robert W. Swisshelm <swisshelm_at_lilly.com>
Date: 1995/09/21
Message-ID: <43rjp8$6td_at_cronkite.d48.lilly.com>#1/1

You can do this in sqlplus using the BREAK and COMPUTE commands. I assume that many other reporting tools also can do summarizations like this.

SQL> break on report
SQL> compute sum of grant_count on report
SQL> select grantee, count(*) grant_count

  2 from dba_role_privs
  3 where grantee like 'S%'
  4 group by grantee;

GRANTEE                        GRANT_COUNT
------------------------------ -----------
SCOTT                                    2
SECURE                                   1
SE_OWNER                                 1
SF36                                     1
SF3_OWNER                                2
SI18678                                  2
SI27972                                  2
SI81659                                  2
SI83057                                  2
SI83483                                  2
SMS_DB2XFER                              1
SPHINX_OWNER                             1
STP_OWNER                                1
SYS                                      8
SYSTEM                                   2
                               -----------
sum                                     30

15 rows selected. Received on Thu Sep 21 1995 - 00:00:00 CEST