SQL, Subquery error message

Hi Experts,

 I got an error message
Msg 512, Level 16, State 1, Line 20
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

for the part of my query below:

(select count(distinct dr.dealid)
from mmd_feed..dealview dv
inner join MakeMyDeal_com..dealresponse2 dr on dv. dealid=dr.dealid
where dv.dealstatus='Sent'
and dr.[DealerResponseStatus]=4
and cast(dr.timestamp as date) >= @StartDate and cast(dr.timestamp as date) <=@EndDate
group by dr.sentbydealercontactid) as accepteddeals

This sub-query only returns 1 value, not the multiple values as the error message said. Can you help me on this?

Thanks,

----Simplify Deal Activities---------


if object_id('tempdb..#deal')is not null 
drop table #deal
if object_id('tempdb..#accepteddeals')is not null 
drop table #accepteddeals



declare @StartDate date
declare @EndDate date
set @StartDate ='2015-06-1'
set @EndDate ='2015-7-14'

-------------------Deal Activities--------------
SELECT
      dc.respondsto AS type
	, a.SentByDealerContactID
    , d.dealershipname
	, dt.pm
    , SUM (CASE
            WHEN 
				  d.dealstatus='sent'
                  and d.manualdeal='No'
                  and d.alternatedeal='No' THEN
                  1
            ELSE
                  0
      END) AS SentDeal,
 SUM (CASE
            WHEN  d.dealstatus='sent'
                  and d.manualdeal='Yes'
                  and d.alternatedeal='No' THEN
                  1
            ELSE
                  0
      END) AS ManualDeal,
SUM (CASE
            WHEN  d.dealstatus='sent'
                  and d.manualdeal='No'
                  and d.alternatedeal='Yes' THEN
                  1
            ELSE
                  0
      END) AS Alternatedeal,

SUM (CASE
            WHEN  d.dealstatus='Sent'
                  and d.archivedealstatus in ('Customer Sold','Sold- Switched Vehicle')THEN
                  1
            ELSE
                  0
      END) AS SoldDeal,

(select count(distinct dr.dealid)
from mmd_feed..dealview dv
inner join MakeMyDeal_com..dealresponse2 dr on dv. dealid=dr.dealid
where dv.dealstatus='Sent'
and dr.[DealerResponseStatus]=4
and cast(dr.timestamp as date) >= @StartDate and cast(dr.timestamp as date) <=@EndDate
group by dr.sentbydealercontactid) as accepteddeals

from 
mmd_feed..dart dt inner join 
mmd_feed..dealview d on dt.dealerid=d.dealerid
inner join mmd_feed..dealercontact dc on d.dealerid=dc.dealerid 
right join 
(select dr2.dealid, dr2.sentbydealercontactid
,min(dr2.timestamp) as timestamp 
from makemydeal_com..dealresponse2 dr2 inner join mmd_feed..dealview dv on dr2. dealid=dv.dealid
where dr2.sentby is not null and dr2.sentbydealercontactid is not null
and cast(timestamp as date) >= @StartDate and cast(timestamp as date) <=@EndDate
group by dr2.dealid, dr2.sentbydealercontactid
) a on a.sentbydealercontactid=dc.dealercontactid and a.dealid= d.dealid
where 
dc.respondsto in ('New', 'Used', 'Both')
and cast(a.timestamp as date)>= @StartDate and cast(a.timestamp as date) <= @EndDATE
group by dc.respondsto,d.dealershipname,a.SentByDealerContactID, dt.pm

Open in new window

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:
Add a "TOP 1" to the beginning of your subquery and see if that helps.
tanj1035Author Commented:
Thanks for your reply.

like this (select top 1 count(distinct dr.dealid)......)??

The query can run, but the value of "accepteddeals" are all incorrect. Do you have any other suggestions?  Thanks.
Brian CroweDatabase AdministratorCommented:
If there is only one possible return for your subquery then how can it be wrong?  For that matter if your subquery is only capable of returning a single value and there is no correlation with the outer query then you should be able to just declare and set a variable with the results of the query and include that.

DECLARE @AcceptedDeals INT;

SELECT @AcceptedDeals = count(distinct dr.dealid)
from mmd_feed..dealview dv
inner join MakeMyDeal_com..dealresponse2 dr
   on dv. dealid=dr.dealid
where dv.dealstatus='Sent'
   and dr.[DealerResponseStatus]=4
   and cast(dr.timestamp as date) >= @StartDate and cast(dr.timestamp as date) <=@EndDate
group by dr.sentbydealercontactid

Open in new window

Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

tanj1035Author Commented:
The sub-query does not have any correlation  with the outer query. I tried your query, but all "accepted deals" returned null value. Did I revise my query correctly?

This is my revised query.

if object_id('tempdb..#deal')is not null 
drop table #deal
if object_id('tempdb..#accepteddeals')is not null 
drop table #accepteddeals



declare @StartDate date
declare @EndDate date
DECLARE @AcceptedDeals INT;
set @Accepteddeals = (select count(distinct dr.dealid)
from mmd_feed..dealview dv
inner join MakeMyDeal_com..dealresponse2 dr
   on dv. dealid=dr.dealid
where dv.dealstatus='Sent'
   and dr.[DealerResponseStatus]=4
   and cast(dr.timestamp as date) >= @StartDate and cast(dr.timestamp as date) <=@EndDate
group by dr.sentbydealercontactid)
set @StartDate ='2015-06-1'
set @EndDate ='2015-7-14'

-------------------Deal Activities--------------
SELECT
      dc.respondsto AS type
	, a.SentByDealerContactID
    , d.dealershipname
	, dt.pm
    , SUM (CASE
            WHEN 
				  d.dealstatus='sent'
                  and d.manualdeal='No'
                  and d.alternatedeal='No' THEN
                  1
            ELSE
                  0
      END) AS SentDeal,
 SUM (CASE
            WHEN  d.dealstatus='sent'
                  and d.manualdeal='Yes'
                  and d.alternatedeal='No' THEN
                  1
            ELSE
                  0
      END) AS ManualDeal,
SUM (CASE
            WHEN  d.dealstatus='sent'
                  and d.manualdeal='No'
                  and d.alternatedeal='Yes' THEN
                  1
            ELSE
                  0
      END) AS Alternatedeal,

SUM (CASE
            WHEN  d.dealstatus='Sent'
                  and d.archivedealstatus in ('Customer Sold','Sold- Switched Vehicle')THEN
                  1
            ELSE
                  0
      END) AS SoldDeal,

@AcceptedDeals as acceptedeals

from 
mmd_feed..dart dt inner join 
mmd_feed..dealview d on dt.dealerid=d.dealerid
inner join mmd_feed..dealercontact dc on d.dealerid=dc.dealerid 
right join 
(select dr2.dealid, dr2.sentbydealercontactid
,min(dr2.timestamp) as timestamp 
from makemydeal_com..dealresponse2 dr2 inner join mmd_feed..dealview dv on dr2. dealid=dv.dealid
where dr2.sentby is not null and dr2.sentbydealercontactid is not null
and cast(timestamp as date) >= @StartDate and cast(timestamp as date) <=@EndDate
group by dr2.dealid, dr2.sentbydealercontactid
) a on a.sentbydealercontactid=dc.dealercontactid and a.dealid= d.dealid
where 
dc.respondsto in ('New', 'Used', 'Both')
and cast(a.timestamp as date)>= @StartDate and cast(a.timestamp as date) <= @EndDATE
group by dc.respondsto,d.dealershipname,a.SentByDealerContactID, dt.pm

Open in new window

Brian CroweDatabase AdministratorCommented:
You will need to use "SELECT" instead of "SET" since you are using a query (reference to SET vs. SELECT https://www.mssqltips.com/sqlservertip/1888/when-to-use-set-vs-select-when-assigning-values-to-variables-in-sql-server/).  Also you will need to set the values for @StartDate and @EndDate before you calculate @AcceptedDeals since they are referenced.

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
Anoo S PillaiCommented:
Could you verify your statement  "This sub-query only returns 1 value, not the multiple values as the error message said."

There is one GROUP BY clause in your offending SELECT statement  ( "group by dr.sentbydealercontactid" ) , So possible are the chances that multiple values are outputted for each dr.sentbydealercontactid. This could be verified by selecting  " select MIN( dr.dealid) " and " select MAX( dr.dealid) " instead of " select count(distinct dr.dealid) " . If these values MIN( dr.dealid)  and  MAX( dr.dealid)  differ, it is evident that more than one value is outputted from the inner query.
PortletPaulEE Topic AdvisorCommented:
SELECT
                  COUNT(DISTINCT dr.dealid)
            FROM mmd_feed..dealview dv
            INNER JOIN MakeMyDeal_com..dealresponse2 dr
                  ON dv.dealid = dr.dealid
            WHERE dv.dealstatus = 'Sent'
            AND dr.[DealerResponseStatus] = 4
            AND CAST(dr.timestamp AS date) >= @StartDate
            AND CAST(dr.timestamp AS date) <= @EndDate
           GROUP BY dr.sentbydealercontactid

The bold line above is the source of your problem just as Anoo (above) has identified.

My guess is you need to move this subquery into your FROM clause and also include the column dr.sentbydealercontactid into the select clause of that subquery so you can join to it.

FROM ....
LEFT OUTER JOIN (
            SELECT
                   dr.sentbydealercontactid
                ,  COUNT(DISTINCT dr.dealid) AS accepteddeals
            FROM mmd_feed..dealview dv
            INNER JOIN MakeMyDeal_com..dealresponse2 dr
                  ON dv.dealid = dr.dealid
            WHERE dv.dealstatus = 'Sent'
            AND dr.[DealerResponseStatus] = 4
            AND CAST(dr.timestamp AS date) >= @StartDate
            AND CAST(dr.timestamp AS date) <= @EndDate
           GROUP BY dr.sentbydealercontactid

            ) AS X ON ???.?????? = X.sentbydealercontactid

You may even be able to simplify it further.

===========
Regarding the date range. Please do NOT alter the data to suit the parameters. Instead, move or alter the parameters to suit the data; it's way more efficient that way.

e.g. if you just add one day to end date and only look for LESS THAN, then you do not have to alter the data.

            WHERE dv.dealstatus = 'Sent'
            AND dr.[DealerResponseStatus] = 4
            AND (
                      dr.timestamp AS date >= @StartDate
            AND dr.timestamp AS date < dateadd(day,1,@EndDate)
                    )

for more on this topic please see: "Beware of Between"
Vitor MontalvãoMSSQL Senior EngineerCommented:
tanj1035, do you still need help with this question?
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.