| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: Another Query Problem
hope this should help
select a.string, b.qty + c.qty + d.qty
from tbl_of_unique_strings a
,(select count(*) qty from first_tbl where x = a.string) b
,(select count(*) qty from first_tbl where y = a.string) c
,(select count(*) qty from first_tbl where z = a.string) d
where b.qty + c.qty + d.qty >= 3
cheers,
arnold
In article <3A252951.4E807252_at_yahoo.ca>,
Ted Wasserman <ted_wasserman_at_yahoo.ca> wrote:
> Sorry, I was unclear in what I meant:
>
> What i want the query to return is a list of (unique) character string
thingies
> (taken from all 3 columns) and a count of how many times they appeared
in all
> the columns (i.e #times in X + #times in Y + #times in Z).
>
> As an additional contraint, I was asking to only display those that
appeared 3
> or more times.
>
> Hope this helps!
>
> -TW
>
> Conan wrote:
>
> > Not sure if I fully understood the question but try this
> >
> > select tab1.col1,count(tab1.col1)
> > from
> > (select x col1
> > from table1
> > union all
> > select y
> > from table1
> > union all
> > select z from table1) tab1
> > group by col1
> > having count(tab1.col1) > 2
> >
> > HTH
> >
> > Conan
> >
> > sw_at_weinerfamily.org wrote in message
<3A2445BB.D7BE4FF4_at_weinerfamily.org>...
> > >Can you let me know if you get an answer - I have the same issue.
> > >
> > >Thanks!
> > >
> > >Regards,
> > >
> > >- Scott
> > >
> > >Ted Wasserman wrote:
> > >
> > >> Thanks to anyone who can help with this one:
> > >>
> > >> I have a table with 3 Columns, X,Y,Z (each of those columns
consists of
> > >> the same datatype, with repeating values in each column and
across the
> > >> columns - in this case it is character strings such as 'a1',
'j5', 'k8'.
> > >> So the table would look something like this:
> > >>
> > >> X | Y | Z
> > >> -------------------
> > >> 'a1' | 'b1' | 'c1'
> > >> 'c1' | 'd5' | 'j9'
> > >> 'f2' | 'a1' | 'c1'
> > >>
> > >> etc.
> > >> Another table also exists listing all the character strings that
may be
> > >> used.
> > >>
> > >> I want a query that will return a list of (unique) character
strings, if
> > >> they appeared in any of the columns 3 or more times (+ the # of
times
> > >> they appeared overall in those columns)
> > >>
> > >> ie.
> > >>
> > >> CHARSTRING | #timesappeared
> > >> -------------------
> > >> 'a1' | 2
> > >> 'b1' | 1
> > >> 'c1' | 3
> > >>
> > >> etc....
> > >>
> > >> Good Luck!
> > >>
> > >> -TW
> > >
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Wed Nov 29 2000 - 14:45:45 CST
 |
 |