How can I group by this?

[lmsook]

Hello I have a table called table_A, and it has several columns and looks like this

id    	s_id    s_name	s_type	created_date
1	10	testA	typeA	2009-01-01
2	10	testA	typeA	2009-01-02
3	10	testA	typeA	2009-01-03
4	10	testA	typeA_1	2009-01-04
5	11	testB	typeB	2009-01-05
6	11	testB	typeB_1	2009-01-06
7	11	testB	typeB_1	2009-01-07
8	12	testC	typeC	2009-01-08
9	12	testC	typeC	2009-01-09
10	12	testC	typeC	2009-01-10
11	12	testC	typeC	2009-01-11
:	:	:	:	:
:	:	:	:	:

s_type should be same for same s_id and s_name, so for s_id:10 and s_name:testA,
s_type should be one type,either typeA or typeA_1, but data in the table is not.
And I need to figure out what s_id and s_name have more than one set of s_type.
In the above example, I would like to have following results from a query

s_id	s_name	s_type	count	
10	testA	typeA	3	
10	testA	typeA_1	1
11	testB	typeB	1
11	testB	typeB_1	2

Maybe it's okay not to show count if it's hard to implement.

As you see, in the results, there should not have s_id:12 s_name:testC, because s_id:12 s_name:testC has one type of s_type which is typeC.

I've tried several sql, but no success.

Thanks for your help in advance.

[oddz]

see below

[oddz]

SELECT
     s_id
     ,s_name
     ,s_type
     ,COUNT(*) AS s_type_total
  FROM
     TABLE_A
 GROUP
    BY
     s_id
     ,s_type

^ Isn't going to drop a s_id with one type though. Missed that part.

[lmsook]

Thanks for your reply.
I think I should modify the original example.

id    	s_id    	s_name	s_type	created_date
1	10	testA	typeA	2009-01-01
2	10	testA	typeA	2009-01-02
3	10	testA	typeA	2009-01-03
4	10	testA	typeA_1	2009-01-04
5	11	testB	typeB	2009-01-05
6	11	testB	typeB_1	2009-01-06
7	11	testB	typeB_1	2009-01-07
8	12	testC	typeA	2009-01-08
9	12	testC	typeA	2009-01-09
10	12	testC	typeA	2009-01-10
11	12	testC	typeA	2009-01-11
:	:	:	:	:
:	:	:	:	:

As you see, s_type(typeA) can be appeared again with other s_id and s_name pear.
The point is that for s_id/s_name, it should be one s_type.
so

GROUP BY s_id,s_type 

won't work.
And there are sooooooo many records in that table, I really want to see only the records whose s_id/s_name has more than one s_type in the results so I can investigate how it did get more than one s_type for s_id/s_name.

Thanks for your help in advance.

[swampBoogie]

select s_id,
       s_name,
       s_type,
       count(*)
  from TABLE_A
  join (select s_id,
               s_name
          from TABLE_A
         group by s_id,
                  s_name
        having count(distinct s_type) > 1) dt
    on dt.s_id = TABLE_A.s_id
   and dt.s_name = TABLE_A.s_name
 group by s_id,
          s_name,
          s_type

[lmsook]

Thanks!!
It works beautifully, except I found I didn't put some condition in the previous example.
So I think I should modify the original example again.

id    	s_id    s_name	s_type	created_date
1	10	testA	typeA	2009-01-01
2	10	testA	typeA	2009-01-02
3	10	testA	typeA	2009-01-03
4	10	testA	typeA_1	2009-01-04
5	11	testB	typeB	2009-01-05
6	11	testB	typeB_1	2009-01-06
7	11	testB	typeB_1	2009-01-07
8	12	testC	typeA	2009-01-08
9	12	testC	typeA	2009-01-09
10	12	testC	typeA	2009-01-10
11	12	testC	typeA	2009-01-11
12	13	testA	typeB	2009-01-12
13	13	testA		2009-01-13
14	13	testA	NULL	2009-01-14

I added one more s_id/s_name set which is 13/testA, and their s_type are typeB,(blank),NULL
Here we ignore blank and NULL s_type, so s_id:13 s_name:testA has really one s_type which is typeB, so it(s_id:13 s_name:testA) should not appear in the results.

How can I do that? Maybe it's too complicated?

[swampBoogie]

select TABLE_A.s_id,
       TABLE_A.s_name,
       TABLE_A.s_type,
       count(*)
  from TABLE_A
  join (select s_id,
               s_name
          from TABLE_A
         where s_type is not null
           and trim(trailing ' ' from s_type) <> ''
         group by s_id,
                  s_name
        having count(distinct s_type) > 1) dt
    on dt.s_id = TABLE_A.s_id
   and dt.s_name = TABLE_A.s_name
 where TABLE_A.s_type is not null
   and trim(trailing ' ' from TABLE_A.s_type) <> ''
 group by TABLE_A.s_id,
          TABLE_A.s_name,
          TABLE_A.s_type

[lmsook]

That's perfect!!

Thanks so much.