Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345

Need Help with the Error 'Subquery returned more than 1 value'. Expand / Collapse
Author
Message
Posted Monday, April 16, 2012 7:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:59 AM
Points: 45, Visits: 342
Distinct does not work but Top 1 works. I only change TOP 1 in the first 2 SET statements and it works, third and fourth statements i didn't change anything.

thanks.
Post #1284105
Posted Wednesday, June 12, 2013 4:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 1:50 AM
Points: 61, Visits: 83
can anyone help me? i am new to db....
CREATE proc [dbo].[student_marks]
@roll_no varchar(20)
AS
BEGIN
CREATE TABLE #temp
(
semester INT,
marks INT
)

INSERT INTO #temp
(
semester,
marks
)
SELECT
semester,
( SELECT SUM ( marks )/count(noof_sub)
FROM student_details sd
INNER JOIN student_marks sm ON sd.roll_no = sm.roll_no
WHERE sd.roll_no = @roll_no
AND ri.semester = sm.sem_attended
group by semester

) marks
FROM student_details sd
INNER JOIN student_marks sm ON sd.roll_no = sm.roll_no
WHERE sm.roll_no=@roll_no
group by semester

SELECT *FROM #temp
end

the problem is when i execute the query it returns "subquery returned more than 1 value' error".because of that subquery contains 3 semesters and marks.when i remove the group by function in subquery it returns the same value for 3 rows.
but i need the result like semester marks
1 82
2 75
3 60
what should i do to overcome this problem????thanks in advance
Post #1462536
Posted Wednesday, June 12, 2013 4:37 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:22 AM
Points: 3,007, Visits: 3,196
As Grass was told - we really need to see the sample data you are running this query against in order to make any meaningful suggestions.


-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Post #1462551
Posted Wednesday, June 12, 2013 4:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 1:50 AM
Points: 61, Visits: 83
i don't understand,what you mean by sample data???
Post #1462553
Posted Wednesday, June 12, 2013 7:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 13,103, Visits: 11,933
nitha jen (6/12/2013)
i don't understand,what you mean by sample data???


Sample data as outlined by reading the article found at the first link in my signature. Additionally you really should start your own thread for your question instead of jumping into somebody else's.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1462614
Posted Wednesday, June 12, 2013 10:58 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:48 AM
Points: 1,118, Visits: 1,582
nitha jen (6/12/2013)
can anyone help me? i am new to db....
CREATE proc [dbo].[student_marks]
@roll_no varchar(20)
AS
BEGIN
CREATE TABLE #temp
(
semester INT,
marks INT
)

INSERT INTO #temp
(
semester,
marks
)
SELECT
semester,
( SELECT SUM ( marks )/count(noof_sub)
FROM student_details sd
INNER JOIN student_marks sm ON sd.roll_no = sm.roll_no
WHERE sd.roll_no = @roll_no
AND ri.semester = sm.sem_attended
group by semester

) marks
FROM student_details sd
INNER JOIN student_marks sm ON sd.roll_no = sm.roll_no
WHERE sm.roll_no=@roll_no
group by semester

SELECT *FROM #temp
end

the problem is when i execute the query it returns "subquery returned more than 1 value' error".because of that subquery contains 3 semesters and marks.when i remove the group by function in subquery it returns the same value for 3 rows.
but i need the result like semester marks
1 82
2 75
3 60
what should i do to overcome this problem????thanks in advance


Hi nitha,

Welcome to SSC......a few things:

1. Check the link in my signature to know how to post in forums.
2. Please don't hijack threads. Its already a 5 page thread and your question would get lost.
3. For fast and better solutions please post a new thread.

Please post DDL and sample data as shown in the link in my signature. Help us help you. Hope you have a good time on SSC.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1462901
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse