Solved

stored procedure - sum??

Posted on 2003-11-03
9
317 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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
 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Row insertion failed. Array 5 48
SQL Script to Remove Data from Two Joined Tables 1 20
Database Integrity 1 50
Substring works but need to tweak it 14 16
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

830 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