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
Mr or Mrs. 500
Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)

Group: General Forum Members
Points: 569 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-Addicted
SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)

Group: General Forum Members
Points: 465 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
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11199 Visits: 4865
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-Addicted
SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)

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

Group: General Forum Members
Points: 149457 Visits: 18575
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
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8585 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