topic Re: Oracle Query Help in Operating System - HP-UX

Oracle Query Help

Prabhu_7 — Wed, 02 Jul 2003 17:17:51 GMT

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

Re: Oracle Query Help

Umapathy S — Wed, 02 Jul 2003 17:24:07 GMT

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

Re: Oracle Query Help

Brian Crabtree — Wed, 02 Jul 2003 19:05:47 GMT

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

Re: Oracle Query Help

Yogeeraj_1 — Thu, 03 Jul 2003 03:34:29 GMT

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

Re: Oracle Query Help

Graham Cameron_1 — Thu, 03 Jul 2003 10:51:51 GMT

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