SQL Query with Self Join

[shahjapan]

HI,
I have a problem to make SQL query which describe below
Problem
Table : TbData

AutoID | ID1 | ID2 | Status
1 10 1 1

2 10 2 0
3 11 1 1

411 2 1

I want output like this :

ID1 | Count(Status(1) ) | Count(Status(0) )
10 | 1| 1

11| 2 | 0

How to make query for this ?

I have to solve this problem very urgent so please if u hav any idea regarding this then guide me
thanks in advance

[Dave44]

I dont think this is a self join. looks like you are simply grouping by ID1 and getting a count of the status.

[code=oracle]
[155]dave@> CREATE TABLE t (autoid NUMBER, id1 NUMBER, id2 NUMBER, status NUMBER);

Table created.

Elapsed: 00:00:00.09
[155]dave@>
[155]dave@> INSERT INTO t
2 VALUES (1, 10, 1, 1);

1 row created.

Elapsed: 00:00:00.00
[155]dave@> INSERT INTO t
2 VALUES (1, 10, 2, 0);

1 row created.

Elapsed: 00:00:00.00
[155]dave@> INSERT INTO t
2 VALUES (1, 11, 1, 1);

1 row created.

Elapsed: 00:00:00.00
[155]dave@> INSERT INTO t
2 VALUES (1, 11, 2, 1);

1 row created.

Elapsed: 00:00:00.01
[155]dave@>
[155]dave@> SELECT id1,
2 SUM( DECODE(status,
3 1, 1,
4 0) ) status_1,
5 SUM( DECODE(status,
6 0, 1,
7 0) ) status_0
8 FROM t
9 GROUP BY id1
10 ORDER BY id1;

ID1 STATUS_1 STATUS_0
---------- ---------- ----------
10 1 1
11 2 0

Elapsed: 00:00:00.01
[/code]