joins and counts

[hypo]

hi, im not an sql guru and i hope someone can help ... this is my problem: I need to get the info from the original table to the destination table ... the question is how? (im working with ingres so please only standard sql)
A bit more info: In the original table: Each row contains a problem which is logged on a certain date and has a status. Now i want the dates grouped, and for each status a count on a certain date (see destination table)

original table:
log_date     |  status
14/feb/06	CLOSED
14/feb/06	OPEN
14/feb/06	WAITING
16/feb/06	WAITING
16/feb/06	WAITING
16/feb/06	CLOSED
16/feb/06	WAITING
16/feb/06	WAITING
16/feb/06	UNSOLVED
16/feb/06	UNSOLVED
17/feb/06	CLOSED
19/feb/06	WAITING
19/feb/06	WAITING
19/feb/06	CLOSED
19/feb/06	WAITING
19/feb/06	UNSOLVED
20/feb/06	UNSOLVED
20/feb/06	UNSOLVED
20/feb/06	WAITING
21/feb/06	CLOSED
21/feb/06	OPEN
21/feb/06	OPEN

destination table:
date           open    closed    unsolved    waiting
14/feb/06      1       1         0           1
16/feb/06      0       1         2           4
17/feb/06      0       1         0           0
19/feb/06      0       1         1           3
20/feb/06      0       0         2           1
21/feb/06      2       1         0           0

I realy hope someone can give me a good query to solve this problem ... big thanks in advance!!

 

[rtmpxr]

select distinct t.name,
(select count(cod) from tabela3 t3 where t3.cod='OPEN' and t3.name=t.name) as "OPEN",
(select count(cod) from tabela3 t3 where t3.cod='CLOSED' and t3.name=t.name) as "CLOSED",
(select count(cod) from tabela3 t3 where t3.cod='UNSOLVED' and t3.name=t.name) as "UNSOLVED",
(select count(cod) from tabela3 t3 where t3.cod='WAITING' and t3.name=t.name) as "WAITING"
from tabela3 t order by t.name

Hope that help.