Solved

stored procedure - sum??

Posted on 2003-11-03
9
312 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Parse field in SQL View 15 97
ASP.NET 5 Templates 2 66
SQL Server Question 5 26
SQL Server merge records in one table 2 12
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

920 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

13 Experts available now in Live!

Get 1:1 Help Now