Re: SQL : How to formulate the query

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Thu, 2 Dec 1999 11:00:14 +0100
Message-ID: <825ftb$7blh$1@oceanite.cybercable.fr>

Something like that:

v734> create table t (f1 number, f2 varchar2(4));
v734> insert into t values(1,'a');
v734> insert into t values(1,'c');
v734> insert into t values(2,'a');
v734> insert into t values(2,'b');
v734> insert into t values(3,'b');
v734> insert into t values(3,'c');
v734> insert into t values(4,'c');
v734> insert into t values(4,'a');
v734> commit;
v734> select a.f2||' with '||b.f2||' = '||count(*) val

  2 from t b, t a
  3 where b.f1 = a.f1
  4 and b.f2 > a.f2
  5 group by a.f2, b.f2
  6 /

VAL

---

a with b = 1
a with c = 2
b with c = 1

3 rows selected.

--
Have a nice day
Michel

Chua Boon Yiang <chuaby_at_hotmail.com> a écrit dans le message : 824he2$rkg$1_at_newton3.pacific.net.sg...
> Hi
> presume i have these 2 fields in a table :
>
> F1 F2
> === ===
> 1 a
> 1 c
> 2 a
> 2 b
> 3 b
> 3 c
> 4 c
> 4 a
>
> I would like to get a stat of "how many with 'a' have 'b' or 'c' "
>
> eg
>
> a with b with
> b = 1 c = 1
> c = 2
>
> May i know how should i roughly come out with the SQL ?
>
> Thanks
> Boon Yiang
>
>
Received on Thu Dec 02 1999 - 04:00:14 CST