count & group by

[poe]

i have a db that is like this:

4 fields (linkid, cat, plan, YesNo)

linkid cat plan YesNo

1 1 a n

2 3 a y

3 3 c y

4 1 b n

5 1 a y

6 1 b n

7 1 c n

8 3 c y

9 3 a n

10 1 a n

 

 

i want to show the count totals of each combination in a matrix type table.

where i list my cats down the left column (now my db does not have any of cat 2, but i still want to show it in my matrix)

i want to show the plans accross the cop ( a, b, c)

and for each plan show how many (y and n)

so for example for cat 1 - plan a, i have 1'y' and 2'n'

 

  - - - - - - -  plans  - - - - - - -

    a            b            c

cat   y | n      y | n      y | n

-------------------------------------------

1   1 | 2      0 | 2      0 | 1

2   0 | 0      0 | 0      0 | 0

3   1 | 1      0 | 0      2 | 0

 

any ideas of how i would go about doing this?

 

thanks

chris

 

[fenway]

You just need to use count() with a group by that contains all 3 columns.