SQL & PL/SQL

on 10G

I have a query that goes something like this (all are VARCHAR, no number fields here).

Create Table dnd_freq_dist As
SELECT
       CREDIT_CARD,        COUNT(CREDIT_CARD)   OVER (PARTITION BY CREDIT_CARD) as CREDIT_CARDfreq,
       LOYALTY,            COUNT(LOYALTY)       OVER (PARTITION BY LOYALTY)           as LOYALTYfreq,
       CHANNEL,            COUNT(CHANNEL)       OVER (PARTITION BY CHANNEL)            as CHANNELfreq,
       LFTSPEND,           COUNT(LFTSPEND)      OVER (PARTITION BY LFTSPEND)          as LFTSPENDfreq
       
FROM Table;

So if I had 10,000,000 records and There were 4 mill No and 6 mill Yes for Credit card, that's what the CREDIT_CARDfreq would show for every row, that distribution.

So that works and gives me the info, but now I wanted to derive percentages of distinct values of each COLUMN_freq to the overall number of rows in the table.

CREDIT Card has 2, so that is easy, but LOYALTY could have up to 5, and CHANNEL up to 9, and SPEND as well up to 15 values (no dollars values are there, just labels from prior Case Statements Betwee 0 ~ 100 then somevalue...)

Anyways, so I tried to do something like this:

select 

CREDIT_CARD, CREDIT_CARDFREQ, ROUND(RATIO_TO_REPORT(CREDIT_CARDFREQ) over (),2) as CREDIT_CARD_RATIO

from

(select distinct CREDIT_CARD,CREDIT_CARDFREQ

from dnd_freq_dist);

This works perfectly, the data that I get back is:

CREDIT_CARD	CREDIT_CARDFREQ	CREDIT_CARD_RATIO
"N"	                  7085607	                         0.81
"Y"	                  1715914	                         0.19

but then when I add more columns, to get the others, that's when things no longer make sense, because there are too many rows/columns, so not sure how to do this best ?

while this query works, the end results make no sense anymore.

select 

CREDIT_CARD, CREDIT_CARDFREQ, ROUND(RATIO_TO_REPORT(CREDIT_CARDFREQ) over (),2) as CREDIT_CARD_RATIO,
LOYALTY, LOYHALTYFREQ,        ROUND(RATIO_TO_REPORT(LOYALTYFREQ) over (),2) as LOYALTY_RATIO,
CHANNEL, CHANNELFREQ,         ROUND(RATIO_TO_REPORT(CHANNELFREQ) over (),2) as CHANNEL_RATIO,
LFTSPEND, LFTSPENDFREQ,       ROUND(RATIO_TO_REPORT(LFTSPENDFREQ) over (),2) as LFTSPEND_RATIO

from

(select distinct CREDIT_CARD,CREDIT_CARDFREQ,LOYALTY,LOYALTYFREQ,CHANNEL,CHANNELFREQ,LFTSPEND,LFTSPENDFREQ

from dnd_freq_dist);

What is the best way to approach this, make this right ?