sql question

Hi all,

    I have a table with the following structure:

    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

I know how to produce the result with two individual sql statements. But any body can tell how to consolidate it by a single query.

Please help
thanks
Elton Received on Tue Jun 02 1998 - 21:09:40 CDT