How to makes sum in sql - SQL Server Forums

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 makes sum in sql

Author

Topic

knutso
Starting Member

20 Posts

Posted - 2006-05-05 : 03:53:42

I want to get different sums from an item base. I have taken the number of every order (ord) from an order base and goes to the item-base like this:

Set Rss = Server.CreateObject("ADODB.RecordSet")
sSQL= "SELECT array(sum(numitems)) from oitems where orderid= " & ord
Rss.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText

I have not tried to run the sql- the array is just a dummy to show the idea.

Actually I want to make an array of the sum of items with the same numitems (i.e. the same productnumber)

if the sql runs I will write the array like this:

While Not rss.eof
response.write array(sum(numitems))
'or whatever way I have to write it
rss.Movenext
Wend

...anyone who has a good idea of how to do this?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-05 : 03:57:27

to guess what you want...

select productnumber, sum(numitems) as SumOfItems
from oitems
group by productnumber

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-05 : 04:01:53

Can you post some sample data and the result you want?

Madhivanan

Failing to plan is Planning to fail

knutso
Starting Member

20 Posts

Posted - 2006-05-05 : 05:08:07

Seems we are approaching an elegant solution. This is a sample of something like the final (ord is the ordernumber connected to the items - that I have got from another sql-call, catalogid is the productnumber):

Set Rss = Server.CreateObject("ADODB.RecordSet")
sSQL = "select catalogid, sum(numitems) as SumOfItems from oitems group by catalogid where orderid= " & ord
Rss.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText

.. I have not got it up running yet..
.. if it runs I would like to write out something like this:

response.write sumofitems(catalogid)

.. where sumofitems should be an array. for example catalogid=280 is a special type of car

knutso
Starting Member

20 Posts

Posted - 2006-05-05 : 05:12:17

I get this error:
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'catalogid where orderid= 1580'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-05 : 05:29:09

sSQL = "select catalogid, sum(numitems) as SumOfItems from oitems ~~group by catalogid~~ where orderid= " & ord & " group by catalogid"

knutso
Starting Member

20 Posts

Posted - 2006-05-05 : 05:54:21

Yes, thanks a lot. Now the query is OK, but the idea is not finished yet. Here is the whole idea:

Dim SumOfItems(1000)
Set Rss = Server.CreateObject("ADODB.RecordSet")
sSQL = "select catalogid, sum(numitems) as SumOfItems from oitems where orderid= " & ord & " group by catalogid"

Rss.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText

for i=1 To 500

response.write i & SumOfItems(i) & " "
next
.....

.. the whole idea is to sum up the same catalogid records (that can have different numitems), put them in a sumofitems array and print out

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-05-05 : 06:17:17

look at
recordsets GetRows method
http://docs.sun.com/source/817-2514-10/Ch11_ADO94.html

and GetString method
http://www.4guysfromrolla.com/webtech/121598-1.shtml

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

knutso
Starting Member

20 Posts

Posted - 2006-05-05 : 17:02:40

I have tested it a bit more now. The query runs OK, but it will not do quite what I want. This is the sql:
sSQL = "select catalogid, sum(numitems) as SumOfItems from oitems where orderid= " & ord & " group by catalogid"

it picks out the correct orderid, but when I print the result I cannot see that it has done any sum:
response.write Rss("catalogid") & "Sum:" & Rss("SumofItems")

It prints the correct catalogid and then the numitems, and it prints all the catalogids. It should have printed catalogid only once for each catalogid and summed the corresponding numitems

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-05 : 21:46:53

"It prints the correct catalogid and then the numitems"
where did the numitems come from ? it is not a column in your select statement.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-06 : 02:37:11

Run the query in the SQL Analyser and check whether the output is desired one and if so then you would have missed out somthing in your Front End Code, which displaying the code on ur web page..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

knutso
Starting Member

20 Posts

Posted - 2006-05-06 : 03:36:23

I realize the cause of the problem. It is actually two tables - with two recordsets - the orders is taken from order-table like this:

set rs=server.createobject("ADODB.Recordset")
sql="select orderid from orders where date= " & dDate
Rs.open sql

'then I do the rs like this:
while not rs.eof
ord=rs("orderid")

'and in a nested loop loop I start the new recordset:
set rss=server.createobject("ADODB.Recordset")
sSQL = "select catalogid, sum(numitems) as SumOfItems from oitems where orderid= " & ord & " group by catalogid"
rss.open sql

'when I do the rss-loop it goes like:
while not rss.eof
response.write rss("catalogid") & rss("sumofitems")
rss.movenext
Wend

rs.Movenext
wend

this is the way I have figured things out, but it actually do not do the job. When I write the rss recordset I should have written the total for all orders. Now I only get one order at a time.

.. anyone with a better solution?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-06 : 03:54:54

I dont think you require 2 loops for doing so.. you can use one query .. for the same..

somthing like this ..

select oitems.catalogid, sum(oitems.numitems) as SumOfItems from oitems
Inner Join Orders On Orders.orderid = oitems.orderid Where
Orders.Date =<Date from the front end>

??

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

knutso
Starting Member

20 Posts

Posted - 2006-05-06 : 05:23:39

Yes! That actually did the job! It was amazingly good help. I cut several hundred lines of code into these few lines:

Set Rs = Server.CreateObject("ADODB.RecordSet")

sSQL="select oitems.catalogid, sum(oitems.numitems) as SumOfItems from oitems Inner Join Orders On Orders.orderid = oitems.orderid Where Orders.oshippeddate =" & dDate & " AND Orders.orderid <> 1 group by oitems.catalogid"
Rs.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText

While Not Rs.eof
m2tt(Rs("catalogid"))=Rs("SumofItems")
response.write Rs("catalogid") & "Sum:" & Rs("SumofItems") & "<br>"
rs.Movenext

Wend

The line
m2tt(Rs("catalogid"))=Rs("SumofItems")
seems to work too well to be true, but it actually puts the correct number of items into an array for the item. I have tested for the main item with number 268, and that actually is correct:

Date for order:#05/08/2006#

catalogid
239 Sum:1
240 Sum:3
268 Sum:1
278 Sum:1
303 Sum:1

I will do som more testing....

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-06 : 05:39:39

quote:

seems to work too well to be true, but it actually puts the correct number of items into an array for the item. I have tested for the main item with number 268, and that actually is correct:

Is your Problem Solved??

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

knutso
Starting Member

20 Posts

Posted - 2006-05-06 : 06:14:55

It seems to work amazingly good. All testing up to now is correct. If it had been possible to get the numbers of how many orders there are in the orders file for that spesific date, I also would have liked that. Should not be so difficult. It is a minor thing in comparison with what have been fixed up to now.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-06 : 06:44:53

The Count you can get from GetCount() method of the RecordSet.

and if you want order wise counting then this query will help ..

select oitems.catalogid, sum(oitems.numitems) as SumOfItems,OrderID,Count(1) As 'Order Count' from oitems
Inner Join Orders On Orders.orderid = oitems.orderid Where
Orders.Date =<Date from the front end>
Group by
oitems.catalogid,OrderID

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-06 : 07:17:04

This will give you the number of orders and number of items for a particular category for the specified date


select i.catalogid, sum(i.numitems) as SumOfItems, count(distinct o.orderid) as OrderCount
from   oitems i inner Join Orders o
on     o.orderid = i.orderid 
where  o.Date = <Date from the front end>
group by i.catalogid

knutso
Starting Member

20 Posts

Posted - 2006-05-06 : 08:42:33

Seems to be an interesting sql. I made it like this:
ssql="select i.catalogid, sum(i.numitems) as SumOfItems, count(distinct o.orderid) as OrderCount from oitems i inner Join Orders o on o.orderid = i.orderid where o.Date = " & ddate & " group by i.catalogid"

but it gives this error:

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'count(distinct o.orderid)'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-06 : 08:46:58

Oops. Did not noticed this is MS Access. Sorry I am not familiar with MS Access. Maybe somebody else can help you

knutso
Starting Member

20 Posts

Posted - 2006-05-06 : 09:16:04

I tried the first suggestion, from chiragkhabaria making it like this:

Set Rsx = Server.CreateObject("ADODB.RecordSet")

ssql="select oitems.catalogid, sum(oitems.numitems) as SumOfItems,OrderID,Count(1) As Order Count from oitems Inner Join Orders On Orders.orderid = oitems.orderid Where Orders.oshippeddate =" & DDate & " Group by oitems.catalogid,OrderID"

Rsx.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText

gave this error:
[Microsoft][ODBC Microsoft Access Driver] The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources