Solved
Contributor
Posts: 25

# how to count the values from one column using SQl

[ Edited ]

have a `customer_no` and `class`. I would like to calculate ratio from the same column for each customer.

``ratio= (number of secured/ total number classes)        ``

``customer_no        class1                  unsecured 1                  secured 1                  secured2                  unsecured2                  secured3                  secured3                  unsecured3                  secured3                  unsecured   ``

The output sample will be

``````customer_no   ratio  1              0.666
2              0.50
3              0.50
.
.
.
9999      ``````

Accepted Solutions
Solution
‎10-08-2017 07:24 AM
PROC Star
Posts: 1,218

## Re: how to count the values from one column using SQl

like this?

``````data have;
input customer_no\$ class\$;
datalines;
1 unsecured
1 secured
1 secured
2 unsecured
2 secured
3 secured
3 unsecured
3 secured
3 unsecured
;

proc sql;
create table want as
select customer_no
,sum(case when class='secured' then 1 else 0 end)/count(customer_no) as ratio
from have
group by customer_no;
quit;``````

All Replies
Solution
‎10-08-2017 07:24 AM
PROC Star
Posts: 1,218

## Re: how to count the values from one column using SQl

like this?

``````data have;
input customer_no\$ class\$;
datalines;
1 unsecured
1 secured
1 secured
2 unsecured
2 secured
3 secured
3 unsecured
3 secured
3 unsecured
;

proc sql;
create table want as
select customer_no
,sum(case when class='secured' then 1 else 0 end)/count(customer_no) as ratio
from have
group by customer_no;
quit;``````
Super User
Posts: 6,642

## Re: how to count the values from one column using SQl

I think @draycut gave you the right answer.  Here is just another tool that may come in handy in this situation:

proc freq data=have;

tables customer_no * class / noprint out=counts;

run;

proc print data=counts;

var customer_no percent;

where class='secured';

run;

PROC Star
Posts: 1,218