## How to achieve Product penetration through Group by?

Solved
Frequent Contributor
Posts: 99

# How to achieve Product penetration through Group by?

Dear all,

I'm facing a product penetration problem.

I have a table t1

 CustomerID SavingAccount TermDeposit Loan aaa Y bbb Y Y ccc Y Y ddd Y eee Y

select t1.SavingAccount,count(Savingaccount) as sa, count(*) as allNo,

count(Savingaccount)/count(*) as penetration

from t1 group by savingaccount;

it will present

0    1      0%

4    4     100%

but what I want is

0    5      0%

4    5     80%

I want count(*) to calculate the original number,not the grouped number.

Can anyone give a hand?

Very thanks.

Accepted Solutions
Solution
‎02-14-2012 04:27 AM
Super User
Posts: 10,787

## How to achieve Product penetration through Group by?

It looks like you need sub-query in SQL.

```data have;
input (CustomerID     SavingAccount     TermDeposit     Loan) (: \$40.);
cards;
aaa     . .     Y
bbb     Y     .     Y
ccc     Y     Y .
ddd     Y . .
eee     Y . .
;
run;
proc sql;
create table want as
select sum,(select count(*) from have) as total ,sum/calculated total as percent format=percent8.
from (select count(SavingAccount) as sum from have group by SavingAccount);
quit;

```

Ksharp

All Replies
Frequent Contributor
Posts: 99

## How to achieve Product penetration through Group by?

What I'm trying to ask further is

that when filter/group changed  how to change respectively,

what solution I present is that

when grouped by Savingaccount,

it will show as follows:

 Y 4 nill 1 Total 5

thus the penetration can be easily calculated.

Solution
‎02-14-2012 04:27 AM
Super User
Posts: 10,787

## How to achieve Product penetration through Group by?

It looks like you need sub-query in SQL.

```data have;
input (CustomerID     SavingAccount     TermDeposit     Loan) (: \$40.);
cards;
aaa     . .     Y
bbb     Y     .     Y
ccc     Y     Y .
ddd     Y . .
eee     Y . .
;
run;
proc sql;
create table want as
select sum,(select count(*) from have) as total ,sum/calculated total as percent format=percent8.
from (select count(SavingAccount) as sum from have group by SavingAccount);
quit;

```

Ksharp

Frequent Contributor
Posts: 99

Ksharp,