Home » SQL & PL/SQL » SQL & PL/SQL » count()
count() [message #186356] Mon, 07 August 2006 12:00 Go to next message
cumin
Messages: 82
Registered: August 2005
Member
I want to get a list of members that belong to only one group, along with the name of the associated group. I expect to get the members "piece" and "clip".

drop table t1;
create table t1 (grp varchar2(20), members varchar2(6));
insert into t1 values('box','marble');
insert into t1 values('bag','clip');
insert into t1 values('envelope','thing');
insert into t1 values('drawer','piece');
insert into t1 values('bag','clip');
insert into t1 values('box','marble');
insert into t1 values('bag','marble');
insert into t1 values('envelope','marble');
insert into t1 values('drawer','piece');
insert into t1 values('drawer','piece');
insert into t1 values('drawer','marble');
insert into t1 values('bag','thing');
insert into t1 values('drawer','thing');
--
select * from t1
order by members, grp;

GRP                  MEMBER
-------------------- ------
bag                  clip
bag                  clip
bag                  marble
box                  marble
box                  marble
drawer               marble
envelope             marble
drawer               piece
drawer               piece
drawer               piece
bag                  thing
drawer               thing
envelope             thing



The following does not work:
select grp, members
from t1
where count(distict grp) =1;

where count(distict grp) =1
      *
ERROR at line 3:
ORA-00934: group function is not allowed here

This gets me close:
select members, count(distinct grp) amt
from t1
group by members
order by amt;
--
MEMBER        AMT
------ ----------
clip            1
piece           1
thing           3
marble          4


Is the following the most efficient approach?
select distinct t1.grp, t1.members
from (select members, count(distinct grp) amt
   from t1
   group by members) x, t1
where x.amt =1 and x.members =t1.members;

GRP                  MEMBER
-------------------- ------
drawer               piece
bag                  clip
I am using another strategy right now and the query is going a lot longer than I exected:
BAD APPROACH:
select distinct t1.grp, t1.members 
from t1, (select A.members
   from t1 A, t1 B
   where A.members = B.members
   and A.grp <> B.grp) X 
where t1.members = X.members(+)
and x.members is null;

GRP                  MEMBER
-------------------- ------
drawer               piece
bag                  clip




Re: count() [message #186367 is a reply to message #186356] Mon, 07 August 2006 13:41 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member

select members, count(*)
from t1
group by members;
Re: count() [message #186370 is a reply to message #186356] Mon, 07 August 2006 13:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
to add a restriction based on group functions as count, you have to use HAVING instead of WHERE.
Search for COUNT(*) and HAVING for examples
Re: count() [message #186371 is a reply to message #186356] Mon, 07 August 2006 13:48 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
sql>select members
  2    from t1
  3   group by members
  4   having count(distinct grp) = 1;

MEMBER
------
clip
piece

2 rows selected.
Re: count() [message #186384 is a reply to message #186356] Mon, 07 August 2006 14:40 Go to previous message
cumin
Messages: 82
Registered: August 2005
Member
Perfect, thank you.
Previous Topic: Pass Append 'pipes' || as part of a string
Next Topic: timestamp datatype
Goto Forum:
  


Current Time: Fri Dec 02 22:46:34 CST 2016

Total time taken to generate the page: 0.24936 seconds