Adding all values in one column

I posed this problem a few days ago, but havent been able to generate the results i need.
Suppose my resultset from an sql query gathering totalsales for a given day by a salesrep looks like this:
Lastname totalsales orderID
--------
doe 1403 510
doe 500 680
doe 200 701

using SUM(Accounts.totalsales) is not adding up the totalsales. What do I need to do to add up the totalsales, and then reassign it to a new
field?
netsportsI am not clear on what you are looking for. Can you show an example of the desired output?
|||DECLARE @.sum int
SELECT
@.sum = SUM(TotalSaleS)
FROM
Accounts
This should work.

|||

Terri:

Below is my older post regarding this. I have since put the sql statement in a stored procedure, and got rid of the inline coding:

Dinakar:
How would I use your code in my stored procedure for this?

==

I am trying to get all the sales reps and their combined sales totals for a given queried date, in which i loop thru (using C# while loop) the available sales reps to get their rep IDs, then match it up with their sales results for the day. The portion of my code below is successful in retrieving all the necessary rep IDs; now I have to loop thru the rep IDs and match their sales total for the given day, whereas the each individual sale is represented with the Order_ID. When running this in my Query Analyzer, i notice that it only gives me just one sale per sales rep on the given date, and not the second or third sale if multiple sales exist for the sales rep on this date. Since this is being looped with the While control statement (and it reads the data reader until there are no more available sales rep IDs), what can I add to the While statement to make sure it grabs all the Order_Ids, and then adds them up in the aggregate SUM statement?
string sqlRep = "SELECT SalesRep.ID as repID , SalesRep.LName " +
"FROM SalesRep " +
"WHERE (Terminated IS NULL) AND (tblSalesRep.StartDate < '" + QueriedDay + "') " +
"order by SalesRep.ID asc ";

SqlCommand objCommandDR = new SqlCommand(sqlRep, objConn);
objConn.Open();
///
////-/ = SqlCommand.ExecuteReader();
SqlDataReader drRep = objCommandDR.ExecuteReader();

// main query

while (drRep.Read())
{
repID = drRep.GetInt32(drRep.GetOrdinal("repID"));
LName = drRep.GetString(drRep.GetOrdinal("LName"));
strSQL = "SELECT SalesRep.ID, SalesRep.LName, SUM(Accounts.totalsales) AS totalsalesQueriedDay " +
"FROM SalesRep INNER JOIN " +
"Orders ON SalesRep.ID = Orders.SalesRep_ID INNER JOIN " +
"Accounts ON Orders.ID = Accounts.Order_ID " +
"WHERE Accounts.TDate = '" + QueriedDay + "' AND SalesRep.ID = '" + repID + "' " +
"GROUP BY SalesRep.ID, SalesRep.LName " +
"HAVING (SUM(Accounts.totalsales) >= 0) " +
"ORDER BY SalesRep.LName";
////-/Debug.WriteLine(strSQL);

// create an instance of the command-connxt object
SqlCommand objCommand = new SqlCommand(strSQL, objConn2);
objConn2.Open();

SqlDataReader drTotalsales = objCommand.ExecuteReader();

}

|||In your code if SalesRepID is numeric you dont need to put quotes around it.
"WHERE Accounts.TDate = '" + QueriedDay + "' AND SalesRep.ID = " + repID +
Better yet, use Parameterized Queries. Besides syntax issues, it also helps you against SQL Injection Attacks.|||

The Sql has been set to a stored procedure now with parameters correctly coded and pointed to the sproc. I'm just wondering if there is any help you can provide in my questions I have outlined in this thread.
thanx in advance, netsports

|||Please provide the new code and restate your question. I, for one, have gotten lost.
|||Before I get started about redundancy and waste of bandwidth andprocessing, I am going to start by saying that I'm making the widestassumption in what you're trying to do.
We're all aware of the saying that to Assume makes an ass out of youand me, so please bear with me as I make myself look foolish.
I'm assuming SalesRep.ID is the Primary Key, SalesRep.LName hasabsolutely no bearing on the record's uniqueness, andAccounts.totalsales can actually have negative values.
I'll also rewrite the sql statement within the unnecessary loop thatcreates yet another connection to the same database.. again, I won'tget into that.
SELECT
SalesRep.ID,
SalesRep.LName,
SUM(Accounts.totalSales) AS totalsalesQueriedDay
FROM
SalesRep INNER JOIN Orders
ON SalesRep.ID = Orders.SalesRep_ID
INNER JOIN Accounts
ON Orders.ID = Accounts.Order_ID
WHERE
Accounts.TDate = @.QueryDay -- assuming again that this is a procedure
AND SalesRep.ID = @.SalesRepID -- there I go assuming again
GROUP BY
SalesRep.ID,
SalesRep.LName
HAVING
(SUM(Accounts.totalsales) >= 0)
ORDER BY
SalesRep.LName -- I think this is strange to have a salesrep id with multiple last names, but that's just me.

What I'd suggest is to execute this in Query Analyzer with thedata in place of the parameters to ensure that the data exists, and youcan validate the output. I'm not sure since I never notint.ToString(), or things like that, but you could have an exceptionsomewhere that's halting your process.
Basically, you'd want a resultset that states
EmployeeID EmployeeLastName SumOfSales
100254 WhoCares 10000.00
258642 JoMomma 200000.00

Right?
|||

All SQL Server aggregate functions ignore NULL except COUNT(*) so if totalsales allow NULL add COUNT(*) to SUM. Another option is supper aggregate GROUPING with ROLLUP and CUBE operators. The text below is from the BOL (books online). Hope this helps.
(This example groups royalty and aggregate advance amounts. The GROUPING function is applied to the royalty column.

USE pubs
SELECT royalty, SUM(advance) 'total advance',
GROUPING(royalty) 'grp'
FROM titles
GROUP BY royalty WITH ROLLUP

The result set shows two null values under royalty. The first NULL represents the group of null values from this column in the table. The second NULL is in the summary row added by the ROLLUP operation. The summary row shows the total advance amounts for all royalty groups and is indicated by 1 in the grp column.)