How can I do a Group by

Question

I can do a lot of requests to a house, but only one request can be accepted. If the request is accepted, the request is saved in my San_Negocio table. In my table San_Proposta, I save teh request.

I want to do a query that get all data in san_proposta that has status equals 1, and all data in san_proposta that has all status equals 2 but in san_negocio, has the ValidaVenda different then ValidaCaptacao

San_Proposta

Proposta_Id    Imovel_Id    DataHora                Status
1              2001         2012-03-07 00:00:00     1
2              2001         2012-03-07 00:00:01     1
3              2001         2012-03-07 14:00:00     2
4              2001         2012-03-07 15:00:00     1
5              2002         2012-03-07 00:00:00     1
6              2002         2012-03-07 00:00:02     1

San_Negocio

Negocio_Id     Imovel_Id    Proposta_Id   ValidaVenda    ValidaCaptacao
1              2001         3             0              1

What I Want

Imovel_Id    Number Of Propost With status 1
2001         3
2002         2

I'm trying this query, but the problem is the return. The Imovel_Id is repeated because of my DataHora that are different in each Request (Proposta_Id).

SELECT p.Proposta_Id 
FROM San_Proposta p 
WHERE p.StatusProposta_Id = 1 

UNION 

SELECT p.Proposta_Id 
FROM San_Proposta p 
LEFT JOIN San_Negocio 
ON San_Negocio.Proposta_Id = p.Proposta_Id 
WHERE p.StatusProposta_Id = 2 
AND San_Negocio.ValidaCaptacao <> San_Negocio.ValidaVenda

Answer 1

I didn't test this, but it'll look something like this:

select Imovel_Id, count(*) as "Number Of Propost With status 1" 
FROM San_Proposta 
WHERE Imovel_Id=2001 and Status=1
GROUPBY Imovel_Id

For the update:

select Imovel_Id, count(*) as "Number Of Propost With status 1" 
FROM San_Proposta p LEFT OUTER JOIN San_Negocio n ON p.Imovel_Id=n.Imovel_Id
WHERE p.Status=1
GROUPBY Imovel_Id

You can then proceed to evaluate any other columns in your where clause by updating where:

WHERE p.Status=1 and p.StatusProposta_Id=2

THe syntax for the left outer join might be different for sqlServer

Answer 2

SELECT sn.Imovel_Id,COUNT(sn.Proposta_Id) as Count_Proposta_status
FROM San_Proposta sp,San_Negocio sn
WHERE sp.Imovel_Id = sn.Imovel_Id
AND sp.Imovel_Id=2001
AND sp.Status = 1
AND sn.Proposta_Id is NOT NULL 
GROUP BY sn.Imovel_Id;