How to Sum the weight of the column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to Sum the weight of the column?

Author

Topic

Vaishu
Posting Yak Master

178 Posts

Posted - 2008-01-11 : 06:31:39

HI

The below is the asp code pasted here to explain what I am trying to do. How to Sum the weight of the item group by itemcode which is passed as a parameter

 
Do While Not objRS.EOF 
 
itmcode = objRS("SKU")

sSQL = "SELECT code, numpacks, weight FROM tblproducts WHERE code = '" & itmcode & "'"
         Set rsNumpks = Conn.Execute(sSQL)
         If Not rsNumpks.EOF Then
            numpacks = CDbl(rsNumpks("Numpacks"))
          weigh = CDbl(rsNumpks("Weight"))
        End If
         Set rsNumpks = Nothing
Loop

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-11 : 06:41:38

Try:-
Do While Not objRS.EOF

itmcode = objRS("SKU")

sSQL = "SELECT p.code, p.numpacks, t.totalweight FROM tblproducts p INNER JOIN (SELECT code,SUM(weight) as 'totalweight' FROM tblproducts GROUP BY code)t ON t.code=p.code WHERE p.code = '" & itmcode & "'"
Set rsNumpks = Conn.Execute(sSQL)
If Not rsNumpks.EOF Then
numpacks = CDbl(rsNumpks("Numpacks"))
weigh = CDbl(rsNumpks("Weight"))
End If
Set rsNumpks = Nothing
Loop

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-11 : 07:09:02

quote:

Originally posted by Vaishu

HI

The below is the asp code pasted here to explain what I am trying to do. How to Sum the weight of the item group by itemcode which is passed as a parameter

 
Do While Not objRS.EOF 
 
itmcode = objRS("SKU")

sSQL = "SELECT code, numpacks, weight FROM tblproducts WHERE code = '" & itmcode & "'"
         Set rsNumpks = Conn.Execute(sSQL)
         If Not rsNumpks.EOF Then
            numpacks = CDbl(rsNumpks("Numpacks"))
          weigh = CDbl(rsNumpks("Weight"))
        End If
         Set rsNumpks = Nothing
Loop

Also you should avoid having concatenated sql. Instead make use of stored procedure with input parameters

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2008-01-11 : 10:08:10

Hi
Thanks for the solution but I am getting below error

The sum or average aggregate operation cannot take a nvarchar data type as an argument

weight is a nvarchar in table

quote:
Originally posted by visakh16

Try:-
Do While Not objRS.EOF

itmcode = objRS("SKU")

sSQL = "SELECT p.code, p.numpacks, t.totalweight FROM tblproducts p INNER JOIN (SELECT code,SUM(weight) as 'totalweight' FROM tblproducts GROUP BY code)t ON t.code=p.code WHERE p.code = '" & itmcode & "'"
Set rsNumpks = Conn.Execute(sSQL)
If Not rsNumpks.EOF Then
numpacks = CDbl(rsNumpks("Numpacks"))
weigh = CDbl(rsNumpks("Weight"))
End If
Set rsNumpks = Nothing
Loop

Vaishu
Posting Yak Master

178 Posts

Posted - 2008-01-11 : 10:18:57

Hi

Thank you and solved it like below

SELECT p.code, p.NumPacks, p.weight, t.totalweight
FROM tblProducts p INNER JOIN
(SELECT code, SUM(cast(weight AS float)) AS 'totalweight'
FROM tblproducts
GROUP BY code) t ON t.code = p.code
WHERE (p.code = 'DENE')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources