suhinrasheed
asked on
oracle query to group and count
hi
need a help regarding building a query
i have cust_mast table ;cust_acc_mast table
and cust_acc_type table
i need a query to give result of 3 types of customers say X,Y,Z
The resulting columns should be cust_type,Tot.no of accounts
and also for each type 1)No of customers having only a single accnt of that type
2) no of customers having 2 accounts of a type
3) no.of customers having more than 2 accnts of a particular type
CUST_MAST AND CUST_ACC_MAST LINKED THRUGH CUSTID field
and cust_mast and cust_acc_type linked thurhg cust_type field
I was able to get total count of accounts in each customer type..but thinking a way of splitting this
total into 3 parts--i.e no of customers with single ac of category x/y/z;2 cnts of x/y/z
and finally lets also display total customers under each category
can anyone sugggest an optimal oracle query
need a help regarding building a query
i have cust_mast table ;cust_acc_mast table
and cust_acc_type table
i need a query to give result of 3 types of customers say X,Y,Z
The resulting columns should be cust_type,Tot.no of accounts
and also for each type 1)No of customers having only a single accnt of that type
2) no of customers having 2 accounts of a type
3) no.of customers having more than 2 accnts of a particular type
CUST_MAST AND CUST_ACC_MAST LINKED THRUGH CUSTID field
and cust_mast and cust_acc_type linked thurhg cust_type field
I was able to get total count of accounts in each customer type..but thinking a way of splitting this
total into 3 parts--i.e no of customers with single ac of category x/y/z;2 cnts of x/y/z
and finally lets also display total customers under each category
can anyone sugggest an optimal oracle query
please post sample input data and expected output
ASKER
cust type
------------ tot customers
------------------- ONLY 1 A/C CUSTOMER ONLY 2 A/C CUSTOMERS MORE THAN 2AC
x 1000 500 400 100
y 500 240 200 60
z 100 30 50 20
EXPECTING AN O/P LIKE ABOVE
------------ tot customers
------------------- ONLY 1 A/C CUSTOMER ONLY 2 A/C CUSTOMERS MORE THAN 2AC
x 1000 500 400 100
y 500 240 200 60
z 100 30 50 20
EXPECTING AN O/P LIKE ABOVE
given what input?
ASKER
i didnt understand wat yu meant by input.If you read throug my question you will note the tables and columns i have mentioned and i need to derive a query using that to produce the o/p i gave
x,y,z are data for customer types
there will be lot of customers with a unique 6 digit cust id and a single customer may have many 10 digit customer accnts opened in his name
x,y,z are data for customer types
there will be lot of customers with a unique 6 digit cust id and a single customer may have many 10 digit customer accnts opened in his name
what I meant is, it always helps (a lot!) to have a complete test case.
Not only does it help ensure the expected results are given (because I know the right answer going in) it also helps remove any confusion as to what you are asking.
so given N rows of data you provide, what do you expect the results of those N rows to be.
where N is however many rows would be necessary to build a useful test case.
So, what I think you are asking is....
customer account type
A 1 X
A 2 X
A 3 X
B 4 X
C 5 Y
C 6 Y
D 7 Y
D 8 Y
I think you want to see
X 2 1 0 1
Y 2 0 2 0
Correct?
If so, where do I get those columns from? Can you descibe the input tables?
If that output is not correct for the given input, please correct (and add additional rows as needed to illustrate any speacial cases) and describe the input tables for this case too
Not only does it help ensure the expected results are given (because I know the right answer going in) it also helps remove any confusion as to what you are asking.
so given N rows of data you provide, what do you expect the results of those N rows to be.
where N is however many rows would be necessary to build a useful test case.
So, what I think you are asking is....
customer account type
A 1 X
A 2 X
A 3 X
B 4 X
C 5 Y
C 6 Y
D 7 Y
D 8 Y
I think you want to see
X 2 1 0 1
Y 2 0 2 0
Correct?
If so, where do I get those columns from? Can you descibe the input tables?
If that output is not correct for the given input, please correct (and add additional rows as needed to illustrate any speacial cases) and describe the input tables for this case too
ASKER
you are correct the output which you shown based on your inputs is wat i expected.
Unfortunately since i am not having access to these tables from my current browsing locns i am not able to give yu table structures.But vaguely it would be something like
cust__mast
-------------
custid varchar2(10)
custname varchar2(50)
Cust_acc_typ char(1)
etc
cust_acc_type
------------------
acc_type char(1)
type_descrpn varhcar2(10)
etc
cust_acc_mast
-------------------
custid varchar2(10)
custaccno number
etc
I hope on a rough note you can help me out in deriving a query based on the above and from what you have understood from all our communications.I am keenly interested in the best logic which we can use
Your help is much appreciated in advance
Unfortunately since i am not having access to these tables from my current browsing locns i am not able to give yu table structures.But vaguely it would be something like
cust__mast
-------------
custid varchar2(10)
custname varchar2(50)
Cust_acc_typ char(1)
etc
cust_acc_type
------------------
acc_type char(1)
type_descrpn varhcar2(10)
etc
cust_acc_mast
-------------------
custid varchar2(10)
custaccno number
etc
I hope on a rough note you can help me out in deriving a query based on the above and from what you have understood from all our communications.I am keenly interested in the best logic which we can use
Your help is much appreciated in advance
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.