Click here to monitor SSC
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 Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
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.
neethu payal
neethu payal
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 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
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4471 Visits: 4545
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
neethu payal
neethu payal
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

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

Group: General Forum Members
Points: 16550 Visits: 17004
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)
vinu512
vinu512
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1149 Visits: 1618
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