Solved

SQL Question: Need to display 2 or more different Advisors from same table for each ID.

Posted on 2015-01-18
14
162 Views
Last Modified: 2015-02-08
I need to pull distinct BMW Customers (which is around 5000) from Cars table and then see if these Customers have two or more different Advisors from Advisors Table ( this has millions of records), if so display those records. Customers with SAME Advisors appearing once or more than once  should NOT display. and also it should display the count of Advisors . One more thing format of Custid's is different in both tables. I tried something had poor performance and worked for small test data but not for huge Voulmes of real data. Below is query.  Please Advice? Thanks!

Output:
CustID          AdvisorName            Account              CountofAdvisors
12                  Brad                            XX                          2
12                  Brad                            YY                          2
12                  Ellen                            ZX                          2  
13                  Polly                           XX                           3
13                  Matt                           tt                             3
13                  Sally                           ll                              3

Query that I tried, doesn't give correct output: (maybe I need to fetch Custid's separately from Cars table first)
SELECT custid, advisor, account
  FROM (SELECT a.custid,
                   a.advisor,
                   a.account,
                   COUNT (DISTINCT advisor) OVER (PARTITION BY a.custid) flg
              FROM Customer_Advisors a, customer_cars b
             WHERE     a.custid =TO_NUMBER (REGEXP_REPLACE ( b.custid,'[^0-9]'))
                   AND b.car = 'BMW')
 WHERE flg <> 1;
0
Comment
Question by:Tulip_23
14 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40557119
you post some output and some query, and are missing to show the corresponding input from the 2 input tables...
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 40557262
also if that output is not correct.... what where you expectingto see?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40557372
Perhaps try this:
SELECT
 	  a.custid
	, a.advisor
	, a.account
	, COUNT(DISTINCT a.advisor)
FROM customer_cars b
INNER JOIN Customer_Advisors a ON a.custid = TO_NUMBER(REGEXP_REPLACE(b.custid, '[^0-9]'))
WHERE b.car = 'BMW'
GROUP BY
 	  a.custid
	, a.advisor
	, a.account
HAVING COUNT(DISTINCT a.advisor) > 1

Open in new window


Note you may want a function based index on b.custid to help performance.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40558467
Be aware that whenever you use syntax like this Oracle: "select ... from (select...))" your query will be slow *IF* the inner "select" returns a large quantity of rows.  Why? These intermediate results must be evaluated by Oracle without the benefit of any indexes.  If that result set is large, Oracle will have to write these intermediate results to temporary segments on disk, and that will cause slow performance.

Using the "distinct" keyword in Oracle queries can also cause performance problems (if the column is not indexed) since Oracle will then have to read all of the rows in the table to see how many different distinct values there are.

You should have these indexes to support your query:

Table_name                 Column_name
-----------------------------   ----------------------
Customer_Advisors  custid
customer_cars           BMW
0
 

Author Comment

by:Tulip_23
ID: 40561389
Hi Guys,

Below Code works it  display one Custname per Id , Advisor Count but displays only one Advisor Name. My  need is to see only one customer per id and distinct Advisor count and all Advisors for that ID.
Ex: Customer Liza could have 5 accounts with Advisor Bob helping with 3 accounts and Amy who helped with two.
 But I want to See Liza appear only once and next to it count of distinct Advisors that is 2 and Name of Advisors- Bob and Amy
Ex:
 CustId,     AdvisorsCount,               Advisor Name
 Liza                   2                                       Bob, Amy
 Susan               3                                      Bob, Jimmy, Amy
 
Any Ideas?

SELECT a.custid
 , a.advisor
 , a.advisor_count
 FROM
 ( select distinct custid
 from customer_cars
 where car = 'LEXUS' )c
 join
 (SELECT ca.custid
 , ca.advisor
 , ca.account
 , COUNT(DISTINCT ca.advisor) OVER (PARTITION BY ca.custid) advisor_count
 ,ROW_NUMBER () OVER (PARTITION BY ca.custid ORDER BY ca.advisor) rn
 FROM customer_advisors ca
 ) a
 ON a.custid = c.custid
 --WHERE c.car = 'LEXUS'
 AND a.advisor_count > 1 and rn=1
 ;
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40561437
Try this perhaps:
SELECT
      A.custid
    , A.advisor_count
    , LISTAGG(advisorname, ',') WITHIN GROUP (ORDER BY advisorname) AS advisornames
FROM (
            SELECT DISTINCT
                  CA.custid
                , CA.advisorname
                , COUNT(DISTINCT CA.advisorname) OVER (PARTITION BY CA.custid) ADVISOR_COUNT
            FROM customer_advisors CA
            --WHERE c.car = 'LEXUS' 
      ) A 
GROUP BY
      A.custid
    , A.advisor_count
;

Open in new window

The pity is that LISTAGG() doesn't have a "distinct" option, hence the need for the derived table (A) that uses select distinct.


details:
**Oracle 11g R2 Schema Setup**:

    CREATE TABLE Customer_Advisors
    	(CUSTID INT, ADVISORNAME VARCHAR2(5), ACCOUNT VARCHAR2(2))
    ;
    
    INSERT ALL 
    	INTO CUSTOMER_ADVISORS (CUSTID, ADVISORNAME, ACCOUNT)
    		 VALUES (12, 'Brad', 'XX')
    	INTO CUSTOMER_ADVISORS (CUSTID, ADVISORNAME, ACCOUNT)
    		 VALUES (12, 'Brad', 'YY')
    	INTO CUSTOMER_ADVISORS (CUSTID, ADVISORNAME, ACCOUNT)
    		 VALUES (12, 'Ellen', 'ZX')
    	INTO CUSTOMER_ADVISORS (CUSTID, ADVISORNAME, ACCOUNT)
    		 VALUES (13, 'Polly', 'XX')
    	INTO CUSTOMER_ADVISORS (CUSTID, ADVISORNAME, ACCOUNT)
    		 VALUES (13, 'Matt', 'tt')
    	INTO CUSTOMER_ADVISORS (CUSTID, ADVISORNAME, ACCOUNT)
    		 VALUES (13, 'Sally', 'll')
    SELECT * FROM dual
    ;
    
    
    CREATE TABLE customer_cars 
    	(CUSTID int)
    ;
    
    INSERT ALL 
    	INTO customer_cars  (CUSTID)
    		 VALUES (12)
    	INTO customer_cars  (CUSTID)
    		 VALUES (13)
    SELECT * FROM dual
    ;

**Query 1**:

    SELECT
          A.custid
        , A.advisor_count
        , LISTAGG(advisorname, ',') WITHIN GROUP (ORDER BY advisorname) AS advisornames
    FROM (
                SELECT DISTINCT
                      CA.custid
                    , CA.advisorname
                    , COUNT(DISTINCT CA.advisorname) OVER (PARTITION BY CA.custid) ADVISOR_COUNT
                FROM customer_advisors CA
                --WHERE c.car = 'LEXUS' 
          ) A 
    GROUP BY
          A.custid
        , A.advisor_count
    

**[Results][2]**:
    
    | CUSTID | ADVISOR_COUNT |     ADVISORNAMES |
    |--------|---------------|------------------|
    |     12 |             2 |       Brad,Ellen |
    |     13 |             3 | Matt,Polly,Sally |


**Query 2**:

    SELECT
          CA.custid
        , COUNT(DISTINCT CA.advisorname) ADVISOR_COUNT
        , LISTAGG(advisorname, ',') WITHIN GROUP (ORDER BY advisorname) AS advisornames
    FROM customer_advisors CA
    GROUP BY
          CA.custid
    

**[Results][3]**:
    
    | CUSTID | ADVISOR_COUNT |     ADVISORNAMES |
    |--------|---------------|------------------|
    |     12 |             2 |  Brad,Brad,Ellen |
    |     13 |             3 | Matt,Polly,Sally |



  [1]: http://sqlfiddle.com/#!4/c5f66/7

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Tulip_23
ID: 40563435
Hi PortletPaul,

Thanks for the Detailed Explanation with examples. When I run your code with LISTAGG Function, it throws me error below:
LISTAGG(advisorname, ',') WITHIN GROUP (ORDER BY advisorname) AS advisornames.
Execution (37: 44): ORA-00923: FROM keyword not found where expected

I use 11.5 Version of Ocacle using TOAD (version 11.5.1.2).  Both Client and Toad are same version.
I tried other examples from ORacle Web Site as well using LISTAGG function , gives error.
What do you think could be issue? I used your DDL , exact same code, changed nothing at all, still getting error :( confused!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40563474
Mmmm
 I am not truly expert on differences in Oracle versions and have almost no experience with TOAD (I used it many years ago a bit)

There should be no problem using LISTAGG in Ora 11.5.1.2 that I know of e.g.
; so I conclude it's an issue with TOAD
(but really I don't know for sure)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40563479
I do notice a character at the end of the line that I didn't use

LISTAGG(advisorname, ',') WITHIN GROUP (ORDER BY advisorname) AS advisornames. --<< LOOK HERE

are you sure that hasn't caused the issue?
0
 

Author Comment

by:Tulip_23
ID: 40571911
I've requested that this question be deleted for the following reason:

Found my own Solution.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40571912
Please post that solution
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40585026
My preference is to see the solution from Tulip_23.

Otherwise http:#a40561437 is the only proposed solution (after details of expected result were revealed)

Uncertain if the performance tips by myself http:#a40557372 or markgeer http:#a40558467 were useful at all as there is no subsequent discussion on those points.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now