Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Course Of The Month
8 days, 6 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL Question: Need to display 2 or more different Advisors from same table for each ID.
Posted on 2015-01-18
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;
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;
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
6-
3
- +2
14 Comments
you post some output and some query, and are missing to show the corresponding input from the 2 input tables...
Active today
Perhaps try this:
Note you may want a function based index on b.custid to help performance.
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
Note you may want a function based index on b.custid to help performance.
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
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
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
;
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
;
Active today
Try this perhaps:
details:
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
;
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
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!
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!
Active today
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.
(but really I don't know for sure)
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.
see: http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030
it refers to LISTAGG in 11g (11.2)
; so I conclude it's an issue with TOADit refers to LISTAGG in 11g (11.2)
(but really I don't know for sure)
Active today
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?
LISTAGG(advisorname, ',') WITHIN GROUP (ORDER BY advisorname) AS advisornames. --<< LOOK HERE
are you sure that hasn't caused the issue?
Active today
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.
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.
Featured Post
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
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here.
General principle of Oracle compression
Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query
Introduction
If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them. …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month8 days, 6 hours left to enroll
Earn Certification
Certified Information Systems Auditor (CISA)
$99.00$89.10
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.