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.

[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