I need answer for this can anyone help me with this

Question

Post reply

I need answer for this can anyone help me with this

andrewalex.r

Old Hand

Points: 328
More actions
April 4, 2013 at 6:38 am

#394521

Declare @CurStudent cursor
Declare @Name VARCHAR(100)
Declare @Dept_ID INT
Declare @Department VARCHAR(100)
Declare @Fees NUMERIC(10,4)
Declare @StudentStatus varchar(100)
Declare @DOJ DATETIME
CREATE table #tempTable(TempName VARCHAR(100), TempDept_ID INT,TempDepartment VARCHAR(100),
TempFees NUMERIC(10,4),TempStudentStatus varchar(100),TempDOJ DATETIME )
SET @CurStudent = CURSOR FOR
SELECT Name,Dept_ID,Department,Fees,StudentStatus,DOJ FROM StudentDatamy
OPEN @CurStudent
FETCH NEXT FROM @CurStudent INTO @Name,@Dept_ID,@Department,@Fees,@StudentStatus,@DOJ
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT #tempTable values(@Name,@Dept_ID,@Department,@Fees,@StudentStatus,@DOJ)
FETCH NEXT FROM @CurStudent INTO @Name,@Dept_ID,@Department,@Fees,@StudentStatus,@DOJ
SELECT @Department,SUM(@Fees)AS TotalFee FROM #tempTable
GROUP BY @Department
End
select * from #tempTable
drop table #tempTable
close @CurStudent
Deallocate @CurStudent
I am getting error as Each GROUP BY expression must contain at least one column that is not an outer reference.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you are grouping by a variable instead of the column from the temp table.

this is not allowed:

GROUP BY @Department

there is no need for a cursor in the operation you are doing...as a matter of fact, there's no need for a temp table either.

this returns the results you would get directly from tthe base table...no cursor, no variables, no temp table.

just a single set based operation.

SELECT Department,SUM(Fees) As TotalFee

FROM StudentDatamy

GROUP BY Department

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

andrewalex.r Old Hand Points: 328 More actions · Answer 2

Since i am starter in sql,they ask me to create a table with student data that include (Dept_ID,Name,Department,Fees,StudentStatus,DOJ)

and they ask to retrive the data into another table which contains sum(fees) for each department and distinct name only i need in that table

Lowell SSC Guru Points: 323518 More actions · Answer 3

ok then, here's two hints:

1. your final SELECt belongs outside of the loop, not inside.

2. your final SELECT command with the Department and SUM() should not reference any @variables whatsoever.

if you fix that, your existing code will work.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

andrewalex.r Old Hand Points: 328 More actions · Answer 4

Sorry to disturb you again,Pls check and correct the code

Declare @CurStudent cursor

Declare @Name VARCHAR(100)

Declare @Dept_ID INT

Declare @Department VARCHAR(100)

Declare @Fees NUMERIC(10,4)

Declare @StudentStatus varchar(100)

Declare @DOJ DATETIME

CREATE table #tempTable(TempName VARCHAR(100), TempDept_ID INT,TempDepartment VARCHAR(100),

TempFees NUMERIC(10,4),TempStudentStatus varchar(100),TempDOJ DATETIME )

SET @CurStudent = CURSOR FOR

SELECT Name,Dept_ID,Department,Fees,StudentStatus,DOJ FROM StudentDatamy

OPEN @CurStudent

FETCH NEXT FROM @CurStudent INTO @Name,@Dept_ID,@Department,@Fees,@StudentStatus,@DOJ

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT #tempTable values(@Name,@Dept_ID,@Department,@Fees,@StudentStatus,@DOJ)

FETCH NEXT FROM @CurStudent INTO @Name,@Dept_ID,@Department,@Fees,@StudentStatus,@DOJ

SELECT TempDepartment,SUM(@Fees)AS TotalFee FROM #tempTable

GROUP BY TempDepartment

End

Lowell SSC Guru Points: 323518 More actions · Answer 5

same issues remain...run and test your code, don't just change one thing and re-post it..

test test test!

Lowell (4/4/2013)
ok then, here's two hints:
1. your final SELECT belongs outside of the loop, not inside.

still an issue. find your END, move your last SELECT after it.

2. your final SELECT command with the Department and SUM() should not reference any @variables whatsoever.
if you fix that, your existing code will work.

still an issue. SUM(@Fees)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Since you say are new to sql. Looping and cursors are notoriously slow. When possible a set based approach will perform a lot better than cursor in most situations. There are certainly times where a cursor is appropriate but your example does not appear to be one of those times. If you would like some help removing the cursor from this let me know. I will be happy to help. 😀

_______________________________________________________________

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Really? If all you need from the StudentData in a separate table is a sumary of fees by department this will provide what you want with no temporary table needed to temporarily the data from the source table. Nor is a cursor even needed.

create table dbo.DepartmentFees(

Department varchar(100),

Fees numeric(10,4)

);

insert into dbo.DepartmentFees

select

Department,

sum(Fees)

from

dbo.StudentData

group by

Department;

select

Department,

Fees

from

dbo.DepartmentFees

order by

Department;