Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

oracle query to group and count

Posted on 2008-10-28
7
Medium Priority
?
627 Views
Last Modified: 2013-12-19
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
0
Comment
Question by:suhinrasheed
  • 4
  • 3
7 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 22822636
please post sample input data and expected output
0
 

Author Comment

by:suhinrasheed
ID: 22822895
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22823009
given what input?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:suhinrasheed
ID: 22823050
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22823268
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

0
 

Author Comment

by:suhinrasheed
ID: 22823407
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
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 22823796
try this...
  SELECT cust_acc_typ,
         COUNT(DISTINCT custid) total_customers,
         COUNT(DISTINCT DECODE(custacccnt, 1, custid)) only_1_account,
         COUNT(DISTINCT DECODE(custacccnt, 2, custid)) only_2_accounts,
         COUNT(DISTINCT CASE WHEN custacccnt > 2 THEN custid END) more_than_2_accounts
    FROM (SELECT c.cust_acc_typ,
                 c.custid,
                 a.custaccno,
                 COUNT( * ) OVER (PARTITION BY c.custid) custacccnt
            FROM cust_mast c, cust_acc_mast a
           WHERE c.custid = a.custid)
GROUP BY cust_acc_typ
ORDER BY cust_acc_typ

Open in new window

0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question