-- count.sql
-- Report the distinct values of [column] within [table]
-- [column] and [table] can be expressions, e.g. @count NVL(a,b) "c WHERE d = e"
-- William Robertson: www.williamrobertson.net
--
-- e.g:
-- @count active_yn subscribers
--
-- A COUNT(*)
-- - --------
-- N    39712
-- Y     5830
--      46060
--
-- Also since SQL*Plus positional params are whitespace-delimited,
-- @count col1,col2 tablename
-- also works (&1 = "col1,col2", &2 = "tablename").
--
-- Tip:
-- Since SQL*Plus stores parameters as environment variables, you can omit the
-- second parameter in subsequent queries for the same table: e.g. following the
-- example above, 'table' is already defined in the SQL*Plus session, so you could
-- type:
-- @count ttn_ind
-- This goes for percent.sql as well.

col proportion format 990 hea "%"

DEF column = "&1"
DEF table = "&2"

select &column
     , count(*) total
     , 100 * ratio_to_report(count(*)) over () as proportion
from &table
group by &column
order by 1, 2
/
