Re: sql question

From: Nuno Guerreiro <nuno-v-guerreiro_at_telecom.pt>
Date: Wed, 03 Jun 1998 09:38:12 GMT
Message-ID: <35781973.259757080@news.telecom.pt>

On Wed, 3 Jun 1998 10:09:40 +0800, "Elton Chan" <97980015r_at_polyu.edu.hk> wrote:

> Table1
>
> mid(num), code(char)
> 1 A
> 1 A
> 2 0
> 2 A
> 2 C
> 3 A
> 3 A
> 3 0
> 3 B
> 3 0
>
>In which I want to list out the number of record for distinct mid and the
>occurrance particular character in code. Hence,
>
> count(*) group by mid mid instance of '0'
> 2 1 0
> 3 1 1
> 5 1 2
>

SQL> select count(*),mid,count(decode(code,'0','0',null)) 2 from table_1
3 group by mid;

COUNT(*) MID COUNT(DECODE(CODE,'0','0',NULL))

--------- --------- --------------------------------
        2         1                                0
        3         2                                1
        5         3                                2

Hope this helps,

Nuno Guerreiro Received on Wed Jun 03 1998 - 04:38:12 CDT