Query Problem- Urgent Help Requred

Dear all

I have two tables
Table T1
ID Name
1 A
2 B
3 C
4 D
5 E
Table T2
ID X1 Stat
1 1 F
1 2 F
1 3 F
1 4 F
2 1 F
2 2
2 3 F
2 4 F
3 1 F
3 2 F
3 3

I want output like this
ID Name Count of First three
record of T2 having
F Stat
1 A 3 F
2 B 2 F
3 C 2 F

He Third column display count for continus F coming from top to
bottom, if anything missed inbetween not counted in output

thanx

Bernhard
Mon Jul 21 11:20:01 CDT 2008

Vp schrieb:
> Dear all
>
> I have two tables
> Table T1
> ID Name
> 1 A
> 2 B
> 3 C
> 4 D
> 5 E
> Table T2
> ID X1 Stat
> 1 1 F
> 1 2 F
> 1 3 F
> 1 4 F
> 2 1 F
> 2 2
> 2 3 F
> 2 4 F
> 3 1 F
> 3 2 F
> 3 3
>
> I want output like this
> ID Name Count of First three
> record of T2 having
> F Stat
> 1 A 3 F
> 2 B 2 F
> 3 C 2 F
>
> He Third column display count for continus F coming from top to
> bottom, if anything missed inbetween not counted in output
>
> thanx

SELECT t1.id, t1.name, count(t2.stat) AS count_of_3 ;
FROM t1, t2 ;
WHERE t1.id=t2.id AND t2.x1 <=3 ;
GROUP BY t1.id, t1.name

Hint: don't use "count" as alias for the third column

Regards
Bernhard Sander

Vp
Mon Jul 21 22:54:09 CDT 2008

On Jul 21, 9:20=A0pm, Bernhard Sander <fu...@no.spam> wrote:
> Vp schrieb:
>
>
>
>
>
> > Dear all
>
> > I have two tables
> > Table T1
> > ID =A0 Name
> > 1 =A0A
> > 2 =A0B
> > 3 =A0C
> > 4 =A0D
> > 5 =A0E
> > Table T2
> > =A0ID X1 Stat
> > =A01 =A0 =A01 =A0 F
> > =A01 =A0 =A02 =A0 F
> > =A01 =A0 =A03 =A0 F
> > =A01 =A0 =A04 =A0 F
> > =A02 =A0 =A01 =A0 F
> > =A02 =A0 =A02
> > =A02 =A0 =A03 =A0 F
> > =A02 =A0 =A04 =A0 F
> > =A03 =A0 =A01 =A0 F
> > =A03 =A0 =A02 =A0 F
> > =A03 =A0 =A03
>
> > I want output like this
> > =A0ID =A0 =A0Name =A0 =A0 =A0 =A0 Count of First three
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 record of T2 having
> > F =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Stat
> > 1 =A0A =A0 =A0 =A0 =A0 =A0 =A0 =A0 3 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0F
> > 2 =A0B =A0 =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 F
> > 3 =A0C =A0 =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 F
>
> > He Third column display count for continus F coming from top to
> > bottom, if anything missed inbetween not counted in output
>
> > thanx
>
> SELECT t1.id, t1.name, count(t2.stat) AS count_of_3 ;
> =A0 FROM t1, t2 ;
> =A0 WHERE t1.id=3Dt2.id AND t2.x1 <=3D3 ;
> =A0 GROUP BY t1.id, t1.name
>
> Hint: don't use "count" as alias for the third column
>
> Regards
> Bernhard Sander- Hide quoted text -
>
> - Show quoted text -

I m really sorry, I had done one mistake in output, just consider it
as under

ID Name Count of First three
record of T2 having
Stat
1 A 3 F
2 B 1 F
3 C 2 F