SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need Help with the Error 'Subquery returned more than 1 value'.


Need Help with the Error 'Subquery returned more than 1 value'.

Author
Message
Grass
Grass
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 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.
neethu payal
neethu payal
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 Visits: 94
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 :-):-)
Stuart Davies
Stuart Davies
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7433 Visits: 4823
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 questionThere 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
neethu payal
neethu payal
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 Visits: 94
i don't understand,what you mean by sample data???
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63278 Visits: 17963
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 Modens 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)
vinu512
vinu512
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3701 Visits: 1626
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 ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search