A question about the SUM() Command

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

A question about the SUM() Command

Author

Topic

sfs00784
Starting Member

4 Posts

Posted - 2007-11-29 : 02:13:55

I don't know how to return the sum of Quantity_held, can somebody help me?
Thanks.

SELECT Item.Item_id, Item.Description, Hold.Quantity_held
FROM Item, Hold, Consist
WHERE Item.Item_id = 2
AND Item.Item_id = Hold.Item_id
AND Item.Item_id = Consist.Item_id
AND Consist.City_id = 5

Item_id Description Quantity_held
----------- ------------------- -------------
2 Chang 50
2 Chang 8
2 Chang 1
2 Chang 2
2 Chang 3

(5 row(s) affected)

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-11-29 : 02:32:48

SELECT Item.Item_id, Item.Description, SUM(Hold.Quantity_held) AS Quantity_held
FROM Item, Hold, Consist
WHERE Item.Item_id = 2
AND Item.Item_id = Hold.Item_id
AND Item.Item_id = Consist.Item_id
AND Consist.City_id = 5
GROUP BY Item.Item_id, Item.Description

sfs00784
Starting Member

4 Posts

Posted - 2007-11-29 : 02:47:27

quote:
Originally posted by PeterNeo

SELECT Item.Item_id, Item.Description, SUM(Hold.Quantity_held) AS Quantity_held
FROM Item, Hold, Consist
WHERE Item.Item_id = 2
AND Item.Item_id = Hold.Item_id
AND Item.Item_id = Consist.Item_id
AND Consist.City_id = 5
GROUP BY Item.Item_id, Item.Description

Thanks for your reply, the problem is that my Item.Description is ntext. Or I should use nvarchar(MAX) for that...

Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-29 : 03:27:30

or instead of Item.Description use cast(Item.Description as nvarchar(max))

Madhivanan

Failing to plan is Planning to fail

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-29 : 06:33:08

try this

SELECT i.Item_id, i.Description, h.Quantity_held
FROM Item i inner join Hold h
i.Item_id = h.Item_id inner join consist c on
i.Item_id = c.Item_id
WHERE Item.Item_id = 2
and Consist.City_id = 5

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-29 : 06:37:19

i think you must have misread the question, no? and not read the replies... again?

quote:

Posted - 11/29/2007 : 06:33:08
--------------------------------------------------------------------------------

try this

SELECT i.Item_id, i.Description, h.Quantity_held
FROM Item i inner join Hold h
i.Item_id = h.Item_id inner join consist c on
i.Item_id = c.Item_id
WHERE Item.Item_id = 2
and Consist.City_id = 5

Rahul Arora

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-11-29 : 16:21:29

quote:
Originally posted by elancaster

i think you must have misread the question, no? and not read the replies... again?

Em

Again? Don't you mean still?

Rahul Arora hasn't answered a single question correctly, as far as I can see. Definitely doing more harm than good here - the wrong answers may lead people astray.

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-30 : 01:51:42

quote:
Originally posted by elancaster

i think you must have misread the question, no? and not read the replies... again?
quote:

Posted - 11/29/2007 : 06:33:08
--------------------------------------------------------------------------------

try this

SELECT i.Item_id, i.Description, h.Quantity_held
FROM Item i inner join Hold h
i.Item_id = h.Item_id inner join consist c on
i.Item_id = c.Item_id
WHERE Item.Item_id = 2
and Consist.City_id = 5

Rahul Arora

Em

yeah i m sorry i just misread and thoght to use join instead of simple comparison thats wahy i forgot to use group by to sum up hold.quantity corresponding to item_id,description.

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-30 : 01:58:29

quote:
Originally posted by KenW

quote:
Originally posted by elancaster

i think you must have misread the question, no? and not read the replies... again?

Em

Again? Don't you mean still?

Rahul Arora hasn't answered a single question correctly, as far as I can see. Definitely doing more harm than good here - the wrong answers may lead people astray.

hi kenw iknow you have seen two answers and saing Rahul Arora hasn't answered a single question correctly, as far as I can see.
you don't have any right to make such statement
and even you are not a superman ok.
i am trying this to improve my logics if you think its wrong then straight tell me its wrong i wouldn't mind as i am not much known to sql server. and belive untill unless you commit a mistake you will
will not learn much

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources