cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle Query Help

Prabhu_7
Frequent Advisor

Oracle Query Help

have a table with,

Structure

Cus_num,Savings,Checking,Funds

Data

C100,1,0,0 (1 means customer has that account)
C200,0,1,0
C300,0,0,1
C400,0,1,0
C500,1,0,0


How to find out (need a query),
how many customers have savings
how many customers have checking
how many customers have funds


Thanks
Raj

4 REPLIES
Umapathy S
Honored Contributor

Re: Oracle Query Help

Prabhu,
If I understood your question correctly then,

select count(*) from customers where savings=1
select count(*) from customers where checking=1
select count(*) from customers where funds=1

will do.

HTH,
Umapathy

Arise Awake and Stop NOT till the goal is Reached!
Brian Crabtree
Honored Contributor

Re: Oracle Query Help

select savings,count(*) from table group by savings;
select checking,count(*) from table group by checking;
select funds,count(*) from table group by funds;

You could also do something like:
select savings,checking,funds,count(*) from table group by savings,checking,funds;

This would give you a tabular count of customers with different forms of accounts (ie: customers with savings and checking). You should get 9 rows.

If you need something more specific, post back with exact details.

Thanks,

Brian
Yogeeraj_1
Honored Contributor

Re: Oracle Query Help

hi,

As brian said above, quite difficult to help here unless you feed us additional information.

Anyway, if you are trying to write a query that satisfies all these criteria, you should should have a where condition:

select count (*)
from customers
where savings >1
and checking > 1
and funds >1;

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Graham Cameron_1
Honored Contributor

Re: Oracle Query Help

There are more powerful SQL features nowadays, including CASE.
How about something like:

SELECT
SUM (CASE WHEN SAVINGS > 0 AND SAVINGS IS NOT NULL THEN 1 ELSE 0 END) as "Number with savings",
SUM (CASE WHEN CHECKING > 0 AND CHECKING IS NOT NULL THEN 1 ELSE 0 END) as "Number with checking",
SUM (CASE WHEN FUNDS > 0 AND FUNDS IS NOT NULL THEN 1 ELSE 0 END) as "Number with funds"
FROM YOUR_TABLE

Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.