SQL, Group by does not work on a single row

Hi Experts,

1) @DealerID=307 which has 4 expired deals, the query can return 4

select  dealerid, count(*) as expireddeals
into #ed
from MMD_Feed..Deal
where submittedtimestamp  >= @StartDate and submittedtimestamp <=@EndDate
and dealerid=@DealerID
and dealstatus in ('Expired', 'Expired-Attention')
group by dealerid

2)@DealerID=99 which has 0 expired deal, the query does not return any value.

select  dealerid, count(*) as expireddeals
into #ed
from MMD_Feed..Deal
where submittedtimestamp  >= @StartDate and submittedtimestamp <=@EndDate
and dealerid=@DealerID
and dealstatus in ('Expired', 'Expired-Attention')
group by dealerid

3) @DealerID=99 which has 0 expired deal, the query without group by can return 0

select  count(*) as expireddeals
into #ed
from MMD_Feed..Deal
where submittedtimestamp  >= @StartDate and submittedtimestamp <=@EndDate
and dealerid=@DealerID
and dealstatus in ('Expired', 'Expired-Attention')

So, If I want to the query 3) has dealerID as one column and still return value, how can I do it?

Thanks
tanj1035Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Do you have a Dealer table that has a unique list of DealerID's?  If so...

select Dealer.DealerID, count(Deal.DealerID) as expireddeals
into #ed
from MMD_feed..Dealer AS Dealer
LEFT OUTER JOIN MMD_Feed..Deal AS Deal
      ON Dealer.DealerID = Deal.DealerID
where Deal.submittedtimestamp  >= @StartDate and submittedtimestamp <=@EndDate
      and Deal.dealerid=@DealerID
      and Deal.dealstatus in ('Expired', 'Expired-Attention')
tanj1035Author Commented:
Hi Brian, thanks for your suggestions.

I have tried it. I can return value for dealerid 307, but still not for dealerid 99.
Is there any other way to do it?
Thanks.
Brian CroweDatabase AdministratorCommented:
What is special about DealerID = 99?
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Brian CroweDatabase AdministratorCommented:
Do you have a Dealer table?
tanj1035Author Commented:
dealID99 has 0 expired deals. then, the query 2) does not return any value. Yes, I do have Dealer Table with unique Dealer ID.
Brian CroweDatabase AdministratorCommented:
Is DealerID = 99 in the Dealer table?  If so then my suggestion should work.  If not can you describe when you are getting back?
awking00Information Technology SpecialistCommented:
select dealerid, sum(case when dealstatus = 'Expired' or dealstatus = 'Expired-Attention' then 1 else 0 end) as expireddeals
tanj1035Author Commented:
Hi Brian,

dealerid=99 is in the dealer table. It only returns table header which is "dealerid", "expireddeal", no values.
tanj1035Author Commented:
Hi awking00, thanks for your reply. I tried yours, but not working.
tanj1035Author Commented:
Thanks for everyone's suggestions. I worked it out finally. Please see my query below, although I do not understand that why it is working with @dealerid

declare @dealerid int
set @dealerid=99

select  @Dealerid as DealerId, count(*) as expireddeals
into #ed
from MMD_Feed..Deal
where submittedtimestamp  >= @StartDate and submittedtimestamp <=@EndDate
and dealstatus in ('Expired', 'Expired-Attention')
and dealerid=@Dealerid

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
if you continue to only use the deal table then if any dealer has no deals in a date range that query will not return a record.

if however you do as Brian Crowe suggests (use the dealer table left join the deal table) then as long as the dealerid is valid you will get a single row.
PortletPaulEE Topic AdvisorCommented:
While I do recommend using the LEFT JOIN as proposed by Brian, I think you also need to move most of the conditions into the join, and only use the WHERE clause on the dealer table.

select Dealer.DealerID, count(Deal.DealerID) as expireddeals
into #ed
from MMD_feed..Dealer AS Dealer
LEFT OUTER JOIN MMD_Feed..Deal AS Deal
      ON Dealer.DealerID = Deal.DealerID
     -- these join conditions apply to the deals only
      AND Deal.submittedtimestamp  >= @StartDate and Deal.submittedtimestamp <=@EndDate
      AND Deal.dealstatus in ('Expired', 'Expired-Attention')

-- the where clause should only apply to the dealers
WHERE Dealer.dealerid=@DealerID
tanj1035Author Commented:
My friend gave me this suggestion and it works.
PortletPaulEE Topic AdvisorCommented:
It might work if there is data that meets all the criteria.

If you want to avoid the problem 2 in you question then I recommend you use the left join and move the criteria as I suggested. However the choice is yours and I will leave it at that.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.