Advanced SQL Question - Counting

[WernerCD]

I have a database query that I'm trying to build. Within this query I have 2 columns (Amongst many).

Column X is 3 statuses (O, I, E).
Column Y is 2 options (W, W/O)

I'm trying to build a report that counts the variations. Ultimately I'd like a simple table output (Where ###'s are counts of the variations):

W         W/O
O         ###        ###
I         ###        ###
E         ###        ###

Or some similar output.

I'd prefer to do this with a SQL query (instead of programatically). I've seen some lightweight queries that can do counts, but not the level I'm wanting.

[tesuji]

Hi Werner,

not an easy question yet a solution can be given by WITH-clause.

/* Assuming table xyz contains these rows:

x	y	z
-----------------
e	w	9
e	w	12
e	wo	8
e	wo	10
e	wo	11
e	wo	18
i	w	4
i	w	6
i	w	7
i	w	15
i	w	19
i	wo	5
i	wo	13
i	wo	14
o	w	2
o	w	20
o	wo	1
o	wo	3
o	wo	16
o	wo	17

then query: */

WITH    --<--- he he, this should also be purple-coloured !
 eio_w (eio, w, z) as
   (select x, count(y), sum(z) from xyz where y = 'w' group by x),
 eio_wo (eio, wo, z) as
  (select x, count(y), sum(z) from xyz where y = 'wo' group by x)
  
 select eio_w.eio as 'eio', w, wo,  eio_w.z+ eio_wo.z as 'sum(z)' 
  from eio_w join eio_wo on eio_w.eio = eio_wo.eio order by eio;

/*  gives this result: 

 eio	w	wo	sum(z)
 -----------------------------
  e	2	4	68
  i	5	3	83
  o	2	4	59
  
on databases Oracle, MS Sql server, and Sybase Sql Anywhere. As for sum(z) this should demonstrate that further columns can be processed (can become difficult).*/

New WITH-clause (standard since sql 2003) also exists in MySQL >5. So you should be able to apply above query on MySQL too. I am sure, besides WITH-clause there exist further solutions, for example such ones based on self-joins.

-- tesu