CODEWITHSUNDEEP
sqlsql-serversubquery
Brad
Brad

Reputation: 85

SQL Server error: subquery returned more than 1 value

I am running a query and attempting to count visits in 2016 into one bucket and visits in 2017 into another bucket. The code is similar to below, however I am not sure why I am getting an error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, etc. or when the subquery is used as an expression.

Any help or suggestions how to go about this would be greatly appreciated. Thanks.

select distinct 
    person.personid, 
    person.DOB,
    person.FullName, 
    (select sum(events) 
     from Visit 
     where date between 20170101 and 20171231 
     group by PersonID) as visit2017, 
    (select sum(events) 
     from Visit 
     where date between 20160101 and 20161231 
     group by PersonID) as visit2016
into 
    #temp
from 
    table1 person 
left join 
    table2 visit on person.personid = visit.personid
where 
    visit.date between 20160101 AND 20171231 ;

Upvotes: 0

Views: 757

Answers (6)

J.D. Walker
J.D. Walker

Reputation: 166

I'm assuming here we have one table, Visit, with Date, PersonID as fields, and one table, person, with personid, DOB and FullName as fields.

select person.personid,
       person.dob,
       person.fullname,
       Count(Year(visit.date))
from   person p
       join visit v
         on p.personid = v.personid
where  Year(visit.date) in ( 2016, 2017 )
group  by p.personid,
          p.dob,
          p.fullname,
          Year(visit.date)

This will count the number of visits per year

Upvotes: 0

James Z
James Z

Reputation: 12317

When you do something like this, it's usually better to calculate all the years in one select, so that the table doesn't have to be read several times, and of you use cross apply, you don't need to multiply the results with all the events and use distinct. I'd do something like this:

select 
    person.personid, 
    person.DOB,
    person.FullName,
    visit.visit_2017,
    visit.visit_2018
into 
    #temp
from 
    table1 person
    cross apply (
        select 
            sum(case when date >= '20170101' and date < '20180101' then events end) as visit_2017,
            sum(case when date >= '20160101' and date < '20170101' then events end) as visit_2016
         from 
             Visit 
         where
             date >= '20160101' and date < '20180101' and 
             Visit.PersonID = person.PersonID
    ) visit

It looks a lot more complex, but isn't that difficult to learn. Case inside a sum is useful really often, and with outer apply you can get several results from the same query into the final select, unlike with (select ..) in select part.

I also changed the between into 2 separate >= and <. Between works ok if you're using dates, but if it's a datetime/smalldatetime etc. then you'll miss anything on the last day where time is not 00:00

Upvotes: 0

dfundako
dfundako

Reputation: 8314

The reason you get that error is because the subquery wants 1 result to come back, but your query produces more than 1 row. You can run it by itself to confirm the error.

You could refactor your subquery into joins instead

select distinct 
person.personid, 
peron.DOB,
person.FullName, 
sum(visit.events) AS visit2017, 
sum(visit2.events) AS visit2016

into #temp
from table1 person 
left join table2 visit
    on person.personid=visit.personid
    AND date between 20170101 and 20171231
left join table2 visit2
    on person.personid=visit2.personid
    AND date between 20160101 and 20161231
where (visit.date between 20170101 and 20171231
OR visit2.date between 20160101 and 20161231) 
GROUP BY    
person.personid, 
peron.DOB,
person.FullName;

Upvotes: 0

dani herrera
dani herrera

Reputation: 51665

You forget to limit subqueries to the current person. But, if this was my select I would write it using CTE and joining it:

with visit2017 as 
   (select sum(events) as sum_events, PersonID
    from Visit 
    where date between 20170101 and 20171231 
    group by PersonID ), 
visit2016 as
   (select sum(events) as sum_events, PersonID 
    from Visit 
    where date between 20160101 and 20161231 
    group by PersonID ) 
select distinct 
       person.personid, 
       person.DOB,
       person.FullName, 
       visit2017.sum_events as visit2017,
       visit2016.sum_events as visit2016
into #temp
from table1 person 
left join table2 visit --< I don't understand this joing
on person.personid=visit.personid
left join visit2017 
on person.personid=visit2017.personid    --< important
left join visit2016
on person.personid=visit2016.personid    --< important
where visit.date between 20160101 AND 20171231 ;

Upvotes: 1

SQL_M
SQL_M

Reputation: 2475

Use an alias in the subquery and outer query and add a where clause.

SELECT PersonID,
       ( SELECT sum(events) from Visit AS V where date between 20170101 and   
         T.PersonID = V.PersonID ) AS VV
FROM TableA AS T

Upvotes: 0

Brad
Brad

Reputation: 3591

From your query it looks like this:

select sum(events) from Visit where date between 20170101 and 20171231 group by PersonID

can return more then 1 record because it returns a sum(events) for each personID. Try running this by itself to see if it returns more then one result

Upvotes: 0

Related Questions