Solved

stored procedure - sum??

Posted on 2003-11-03
9
309 Views
Last Modified: 2008-03-06
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
Comment
Question by:jay-are
  • 5
  • 4
9 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9675661
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
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9675844
Do you have this table Comments created already? Does it allow you to insert null values into other columns?
0
 

Author Comment

by:jay-are
ID: 9678823
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
 

Author Comment

by:jay-are
ID: 9680532
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:jay-are
ID: 9680534
oh, and the Comments table does allow nulls
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9684072
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
 

Author Comment

by:jay-are
ID: 9689809
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
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 500 total points
ID: 9691074
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
 

Author Comment

by:jay-are
ID: 9694431
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now