Hi

I'm cuestion
how to update at all
this is my sentence
update Table1
set Total = sum(b.monto)
from Table1 a , Details b
Where a.numerCode = b.numerCode
the answer is : an aggregate may not apper in the set list of an Update
statement.
Thanks for you help.
ChreesHello, MJ
Try something like this:
update Table1 set Total = (
select sum(b.monto) from Details b
Where a.numerCode = b.numerCode
)
from Table1 a
where exists (
select * from Details b
Where a.numerCode = b.numerCode
)
or:
update Table1 set Total = isnull((
select sum(b.monto) from Details b
Where a.numerCode = b.numerCode
),0)
from Table1 a
Razvan|||Thanks
try Ok.
thank you for you help.
"Razvan Socol" escribió:
> Hello, MJ
> Try something like this:
> update Table1 set Total = (
> select sum(b.monto) from Details b
> Where a.numerCode = b.numerCode
> )
> from Table1 a
> where exists (
> select * from Details b
> Where a.numerCode = b.numerCode
> )
> or:
> update Table1 set Total = isnull((
> select sum(b.monto) from Details b
> Where a.numerCode = b.numerCode
> ),0)
> from Table1 a
> Razvan
>

Hi

I'm cuestion
how to update at all
this is my sentence
update Table1
set Total = sum(b.monto)
from Table1 a , Details b
Where a.numerCode = b.numerCode
the answer is : an aggregate may not apper in the set list of an Update
statement.
Thanks for you help.
ChreesHello, MJ
Try something like this:
update Table1 set Total = (
select sum(b.monto) from Details b
Where a.numerCode = b.numerCode
)
from Table1 a
where exists (
select * from Details b
Where a.numerCode = b.numerCode
)
or:
update Table1 set Total = isnull((
select sum(b.monto) from Details b
Where a.numerCode = b.numerCode
),0)
from Table1 a
Razvan|||Thanks
try Ok.
thank you for you help.
"Razvan Socol" escribió:

> Hello, MJ
> Try something like this:
> update Table1 set Total = (
> select sum(b.monto) from Details b
> Where a.numerCode = b.numerCode
> )
> from Table1 a
> where exists (
> select * from Details b
> Where a.numerCode = b.numerCode
> )
> or:
> update Table1 set Total = isnull((
> select sum(b.monto) from Details b
> Where a.numerCode = b.numerCode
> ),0)
> from Table1 a
> Razvan
>

Help: Instance index

I need to create an instance index for a table group by studentid and classid

CREATE TABLE Table1

(ClassID varchar(10),

StudentID varchar(10),

Fee1 money,

fee2 money,

fee3 money

)

INSERT INTO Table1

VALUES ('02003', '00001', 20,10,15)

INSERT INTO Table1

VALUES ('02003', '00001', 25,15,15)

INSERT INTO Table1

VALUES ('02005', '00001', 10,10,15)

INSERT INTO Table1

VALUES ('02005', '00004', 20,10,15)

INSERT INTO Table1

VALUES ('02005', '00004', 20,10,15)

INSERT INTO Table1

VALUES ('02005', '00004', 20,10,15)

SELECT * FROM Table1

the output i'd like to see is :

ClassIDStudentIDFee1Fee2Fee3Index

020030000120.0010.0015.001

020030000125.0015.0015.002

020050000110.0010.0015.001

020050000420.0010.0015.001

020050000420.0010.0015.002

020050000420.0010.0015.003

Any suggestions on what method to use? Thanks.

Here it is,

Code Snippet

SELECT

ClassID,StudentID,Fee1, Fee2, Fee3,

ROW_NUMBER() over (Partition By ClassId, StudentId Order By ClassId, StudentId) as Index

FROM

Table1

|||

Use ROW_NUMBER()...something like this (didn't test it)

select

ClassID

,StudentID

,Fee1

,Fee2

,Fee3

,RANK() OVER (PARTITION BY ClassID, StudentID ORDER BY Fee1) AS 'Index'

from

Table1

|||

Thanks!!