tanj1035
asked on
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
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
ASKER
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.
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.
What is special about DealerID = 99?
Do you have a Dealer table?
ASKER
dealID99 has 0 expired deals. then, the query 2) does not return any value. Yes, I do have Dealer Table with unique Dealer ID.
Is DealerID = 99 in the Dealer table? If so then my suggestion should work. If not can you describe when you are getting back?
select dealerid, sum(case when dealstatus = 'Expired' or dealstatus = 'Expired-Attention' then 1 else 0 end) as expireddeals
ASKER
Hi Brian,
dealerid=99 is in the dealer table. It only returns table header which is "dealerid", "expireddeal", no values.
dealerid=99 is in the dealer table. It only returns table header which is "dealerid", "expireddeal", no values.
ASKER
Hi awking00, thanks for your reply. I tried yours, but not working.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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
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
ASKER
My friend gave me this suggestion and it works.
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.
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.
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')