• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 359
  • Last Modified:

stored procedure - sum??

I'm trying to do something tricky with an asp.net datagrid.  I have a column that shows $ amounts  for each reference number under a customer number.  Problem is that each customer number has multiple reference numbers and amounts.  I need to total all the amounts up to one number so my datagrid isn't 4x as long as it should be.  This would be very simple if the table I was using included each schedule's beginning amount and ending balance...bleh!

In Crystal Reports this is easily done with a select:  Sum ({Accounting.Amount}), {Accounting.Control#}) > $0.00


So how do I accomplish this with a stored procedure?  It would be nice to write the sum'd amount to a table I use called "Comments".  Any help is appreciated!

0
jay-are
Asked:
jay-are
  • 5
  • 4
1 Solution
 
namasi_navaretnamCommented:
drop procedure usp_SumByControlNo
go

create procedure usp_SumByControlNo
As
BEGIN
   
   INSERT INTO Comments (ControlNo, SumAmt)
   SELECT ControlNo, Sum(Amout)
    FROM MyTable    
   GROUP BY ControlNo
   HAVING Sum(Amout) > 0

   IF @@ERROR <> 0
   BEGIN
     RAISERROR('Insert into Comments failed', 16, 1)
     RETURN -1
   END  

RETURN 0

END


HTH
Namasi
0
 
namasi_navaretnamCommented:
Do you have this table Comments created already? Does it allow you to insert null values into other columns?
0
 
jay-areAuthor Commented:
I do have a sql table called Comments.  It has two columns for now:  Control# & CommentBox.  The actual amounts are in a different table called 'Accounting'.  I was hoping I could just create the column called SumAmt and have the real sums there with the correct control number.

I will test your code in a sec.
thanks!
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
jay-areAuthor Commented:
Namasi:

I got pulled off from this for a second but I did test it out.  Its writing the amounts out to the right table and column but its not matching up the amounts with the control numbers.

The accounting table that contains the control numbers and amounts I'm getting the info has the column names:

'Control#'  'Amount'

The Comments table has these columns:
'ControlNo' 'SumAmt'

So I'm doing all this so I can display the amounts summed up in an asp.net editable datagrid.  I also use the seperate table to store some text comments made in the editable datagrid.  With this stored procedure the way it is now I get the amounts listed, but they are with the wrong control numbers.  Also, there are tons of records listed.  Its showing each entry to the database instead of just giving me one record per control number...

any ideas?
0
 
jay-areAuthor Commented:
oh, and the Comments table does allow nulls
0
 
namasi_navaretnamCommented:
You may need to add additional conditions on where clause to restrict the result set. Query below will only display distinct control number and the sum for that control number.

SELECT ControlNo, Sum(Amout)
    FROM MyTable    
   GROUP BY ControlNo
   HAVING Sum(Amout) > 0

BTW, 'Control #' does not look like a column name to me.

0
 
jay-areAuthor Commented:
Namasi

This is what the procedure looks like now:

CREATE procedure usp_SumByControlNo
As
BEGIN
   
   INSERT INTO Comments (ControlNo, SumAmt)
   SELECT Distinct Control#, Sum(Amount)
   FROM Accounting
   GROUP BY Control#
   HAVING Sum(Amount) > 0
 
   IF @@ERROR <> 0
   BEGIN
     RAISERROR('Insert into Comments failed', 16, 1)
     RETURN -1
   END  

RETURN 0

END
GO

It's not really summing up the amounts for a control number.  The data comes from a schedule that has multiple entries made to control numbers.  So a customer gets charged for something, and then they post a payment for it.  So you have the same amounts going in and out in that data.  ie:  Control# 12345 - $15000
                                                 Control# 12345 - <$15000>

So the datagrid I'm using to display this info is showing ever single sum for all entries made.  I'm trying to get the balance of the Amount for every control#.  The total!  
0
 
namasi_navaretnamCommented:
Could you run this query and post the results

SELECT Control#, Sum(Amount)
   FROM Accounting
   GROUP BY Control#
   HAVING Sum(Amount) > 0

And also post the table definition of Accounting table.

also post results of this query.

select * from Accounting where ControlNo = <Any control number from group by select>
0
 
jay-areAuthor Commented:
Namasi:

I modified your query there and ran this instead:

SELECT Control#, Sum(Amount)
   FROM Accounting Where [Acct No] = '1020'
   GROUP BY Control#
   HAVING Sum(Amount) > 0

This gives me exactly what I want!  One control number and the summed amount!  So the problem must be with my datagrid or something, which is a question for a different forum.  Thanks for your help!!
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now