asked on

SQL, How to count "sent deal" by different conditions?

Hi Experts,

I want to calculate the column "sent deal" by the different conditions
1) when dc.respondsto = 'New', count "sent deals" when d.vehiclesstatus = 'New'
2)when dc.respondsto = 'Used', count "sent deals" when d.vehiclesstatus = 'Used'
3)when dc.respondsto = 'Both', count "sent deals" when and d.vehiclesstatus = 'Used'+'New'

I do not know how can I write the query for those 3 conditions, I tried to use "sum case" and I knew it was not correct. Can you help me on it? Thanks.

select dc.respondsto as type,
dr.sentby as DealManager, 
d.dealershipname,
sum (case when dc.respondsto='New'and d.VehicleStatus ='New' then 1 else 0 end) as SentDeal,
sum (case when dc.respondsto='Used'and d.VehicleStatus ='Used' then 1 else 0 end) as SentDeal,
sum (case when dc.respondsto ='Both'and d.VehicleStatus in ('Used', ' New') then 1 else 0 end) as SentDeal
from mmd_feed..dealview d
inner join mmd_feed..dealercontact dc on d.dealerid=dc.dealerid
inner join mmd_feed..dealresponse dr on dr. dealid=d.dealid
where d.dealerid in (select d.dealerid from mmd_feed..dart)
and d.dealstatus='sent'
and d.manualdeal='No'
and d.alternatedeal='No'
and dc.respondsto in ('New', 'Used', 'Both')
and dr.sentby is not null
and d.submittedtimestamp >= '2015-07-01' and d.submittedtimestamp <= '2015-07-14'
group by  dr.sentby,d.dealershipname,dc.respondsto

Open in new window

Brian Crowe

Using SUM/CASE is exactly what I would do. How is it "not correct"?

Scott Pletcher

You can't name three result columns all "SentDeal" and then reference the result, because SQL wouldn't know which column "SELECT SentDeal" refers to.

Try this:

select dc.respondsto as type,
dr.sentby as DealManager,
d.dealershipname,
sum (case when dc.respondsto='New' and d.VehicleStatus ='New' then 1 else 0 end) as SentDeal_New,
sum (case when dc.respondsto='Used' and d.VehicleStatus ='Used' then 1 else 0 end) as SentDeal_Used,
sum (case when dc.respondsto='Both' and d.VehicleStatus in ('Used', ' New') then 1 else 0 end) as SentDeal_Both
from mmd_feed..dealview d
inner join mmd_feed..dealercontact dc on d.dealerid=dc.dealerid
inner join mmd_feed..dealresponse dr on dr. dealid=d.dealid
where d.dealerid in (select d.dealerid from mmd_feed..dart)
and d.dealstatus='sent'
and d.manualdeal='No'
and d.alternatedeal='No'
and dc.respondsto in ('New', 'Used', 'Both')
and dr.sentby is not null
and d.submittedtimestamp >= '2015-07-01' and d.submittedtimestamp <= '2015-07-14'
group by dr.sentby,d.dealershipname,dc.respondsto

tanj1035

ASKER

Thanks for your reply. I have to do new, used and both in the same column " sent deal"

So, I don't know how to do it.

ASKER CERTIFIED SOLUTION