SQL Query to identify customers who are at more than one site

Question

I have a problem with some SQL query here. I want to select customers are in more than one site Example peter >> site1 peter >> site2 peter site3 please i have tried with many query but i thing is not correct i need something more logical to do that in oracle sql plus.

SQL> select * from HHCCUSTOMERS;

   OID O_CNAME           CID O_SITE  O_BOOKDAT    DEPOSIT    PAYMENT

  1220 tess              120 site3   10-DEC-15        500       1500
  1240 hilda             140 site3   30-JAN-16        300       1500
  1250 isla              150 site2   04-JAN-16        750       1000
  1260 hilda             160 site1   05-FEB-16        800       2000
  1280 maija             180 site3   20-NOV-15        650       1500
  1290 iris              190 site3   10-DEC-15        600       1500
  1200 tess              120 site3   10-DEC-15        500       1500
  1202 hilda             140 site2   30-JAN-16        300       1500
  1203 louise            130 site1   20-NOV-15        600       1000

9 rows selected. I used this query

 select o_cname,oid,o_site,c_type,c_facility
      from HHCCUSTOMERS
      inner join HHCPARK
      on HHCPARK.cid=HHCCUSTOMERS.cid
      where o_cname='louise' or o_cname='hilda';

O_CNAME           OID O_SITE  C_TYPE     C_FACILITY
---------- ---------- ------- ---------- ----------
louise           1203 site1   bronze     semifurnis
hilda            1240 site3   silver     digitalvan
hilda            1202 site3   silver     digitalvan
hilda            1260 site1   gold       customize

SQL Plus is presumably Oracle, so you should tag the question as Oracle. — Gordon Linoff, Dec 16 '15 at 11:44

score 2 · Answer 1 · answered Dec 16 '15 at 11:45

If you want the customers, then you can do:

select c.o_cname
from hhcustomers c
group by c.o_cname
having min(o_site) <> max(o_site);

An alternative is:

having count(distinct o_site) > 1

count(distinct) incurs extra overhead, so min()/max() often has slightly better performance.

If you want the details information, then one method is analytic functions:

select c.*
from (select c.*,
             count(distinct o_site) over (partition by o_cname) as numsites
      from hhcustomers c
     ) c
where numsites > 1;

Or, an alternative that doesn't directly count the sites uses exists:

select c.*
from hhcustomers c
where exists (select 1
              from hhcustomers c2
              where c2.o_cname = c.o_cname and c2.o_site <> c.o_site
             );

I did this it worked `select HHCCUSTOMERS.o_cname from HHCCUSTOMERS group by HHCCUSTOMERS.o_cname having count(distinct o_site) > 1;` — Peter Valentine, Dec 16 '15 at 11:52
@PeterValentine . . . An expression that selects all the columns from `c`. — Gordon Linoff, Dec 17 '15 at 03:08

score 0 · Answer 2 · answered Dec 16 '15 at 11:42

0

This sounds like you want to use count(.) and group by; something like

SELECT O_CNAME, COUNT(O_SITE) AS C FROM HHCCUSTOMERS WHERE C > 1 GROUP BY O_CNAME

Note: didn't check this query.

answered Dec 16 '15 at 11:42

Marcus Müller

34,677
4
53
94

It is wrong. You can't use a column of an aggregate function in the same query. – Jorge Campos Dec 16 '15 at 11:44
I just did this `SELECT o_cname COUNT(o_site) AS o_cname FROM HHCCUSTOMERS WHERE o_cname > 1 GROUP BY o_cname;` and get this error ERROR at line 1: ORA-00923: FROM keyword not found where expected – Peter Valentine Dec 16 '15 at 11:47
@JorgeCampos I think you can; reference on StackOverflow: http://stackoverflow.com/a/2722426/4433386 – Marcus Müller Dec 16 '15 at 11:49
@PeterValentine: `COUNT(o_site) AS o_cname` is a bad idea, since you already have a column clalled `o_cname` – Marcus Müller Dec 16 '15 at 11:49

Jorge Campos · Answer 3 · 2015-12-16T11:51:12.263

0

Try this way:

select o_cname, count(*)
  from HHCCUSTOMERS
       inner join HHCPARK
           on HHCPARK.cid=HHCCUSTOMERS.cid
  where o_cname='louise' or o_cname='hilda'
  GROUP BY o_cname
  HAVING count(*)>1;

If you leave the where clause it will check on for louise or hilda if you take the where out it will show everyone who is in more then one place at same time.

edited Dec 16 '15 at 11:51

answered Dec 16 '15 at 11:45

Jorge Campos

22,647
7
56
87

ERROR at line 1: ORA-00937: not a single-group group function – Peter Valentine Dec 16 '15 at 11:50

SQL Query to identify customers who are at more than one site

3 Answers3