Sum problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sum problem

Author

Topic

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2009-02-13 : 11:42:33

I have a SQL statement that I will eventually turned into a stored procedure once I iron out the kinks.

The problem I have having is a summing issue related to the group by.

Currently I have this -


SELECT tblClients.ClientID, tblClients.DBAName, tblClients.Terms, dbo.ASP_AccountBalance2(tblclients.clientid) as BalDue, dbo.ASP_MailingValue(tblcontractdetail.fjobid, tblcontractdetail.contractid, tblcontractdetail.proddate) as CValue
FROM (tblClients INNER JOIN (tblContractDetail INNER JOIN tblContracts ON tblContractDetail.ContractID = tblContracts.ContractID) ON tblClients.ClientID = tblContracts.ClientID) INNER JOIN Mailcoups.dbo.vw_MasterCalendar_MDI ON tblContractDetail.ProdDate = Mailcoups.dbo.vw_MasterCalendar_MDI.cmRunDate
WHERE mailcoups.dbo.vw_MasterCalendar_MDI.cmWeekno=8 and Mailcoups.dbo.vw_MasterCalendar_MDI.cmProdYear=2009 and  tblcontractdetail.status <> 'Invoiced' and  tblcontractdetail.status <> 'Cancelled' and tblcontractdetail.inserttype <> 'W'
GROUP BY tblClients.ClientID, tblClients.DBAName, tblClients.Terms, tblContractDetail.FJobID, tblContractDetail.ProdDate, tblContractDetail.ContractID

Which returns the correct information but in the event that a client has a job with 2 fjobids then I get to listings.

My function asp_mailingvalue gets a sum for the value of everything per an fjobid. But what I want to do is then sum that so if say Client A has fjobid 1 = 200 and fjobid 2 =3 it would return 500 on my sql line instead to separate line items.

If I put a sum(asp_mailingvalue(blah blah blah)) in place it will return the sum of each possible lineitem. So say

Client 1 has 9 jobs which originally sums to 2000 for CValue it will give me 18000 returned based on the sum which is incorrect. It should just be 2000.

any help would be great.

thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-13 : 12:27:19

Can you provide sample data and expected output for clarity?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 12:44:40

give info in this format

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2009-02-13 : 13:15:32

Sure. Sorry about.

Here is a sample with the problem -

Client ID Client Terms BalDue CValue -- hidden from view -- FjobID
569 Southern Express Lubes NULL 0 662 -- 7517
569 Southern Express Lubes NULL 0 2503 -- 7529

The problem comes where there are 2 unique Fjobids. I want to make it only group by ClientID. Since I will control ProdDate, and Year eventually be pass thru values. So In this example Id want to see this -

Client ID Client Terms BalDue CValue
569 Southern Express Lubes NULL 0 3165

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 13:26:48

from limited info provided, i assume it will be like this



SELECT Client ID Client Terms BalDue,SUM(CValue)
FROM Table
GROUP BY Client ID Client Terms BalDue

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2009-02-13 : 13:32:37

See I try that.

But then my results get skewed and cvalue ends up being the cvalue * number of entries that get grouped by.

So say I have a client that has 5 listings for a mailing relating to 1 job. Its Cvalue is 1000. Each worth 200. If I do a sum on Cvalue it then returns 5000 instead of just 1000 which I'd hope it would do. Even though in the listing without the sum it may only have 1 entry in the results.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 13:38:54

i cant understand you unless you provide some data . i'm assuming this might be what you want


SELECT t1.Client ID, t1.Client, t1.Terms, t1.BalDue,t2.Total
FROM Table t1
INNER JOIN (SELECT Client ID,FjobID,SUM(CValue) AS Total
            FROM Table
            GROUP BY Client ID,FjobID) t2
ON t2.Client ID=t1.Client ID
AND t2.FjobID=t1.FjobID

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2009-02-13 : 13:51:58

I thought I provided sample data.

But to combat the issue I decided to instead play with the SQL to adjust my functions to better correspond with the calculation.

This is all set now. I adjusted my function returning CValue to only go off client and date rather than by job which it is doing in another scenerio.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 13:55:37

didnt my previous suggestion worked? i assumed you're interested in sum for each Client ID,FjobID group

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-13 : 16:33:36

quote:
Originally posted by mxfrail

I thought I provided sample data.

But to combat the issue I decided to instead play with the SQL to adjust my functions to better correspond with the calculation.

This is all set now. I adjusted my function returning CValue to only go off client and date rather than by job which it is doing in another scenerio.

You will need to provide all explanation with sample and expected output as described in the link so people can help you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources