• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 127
  • Last Modified:

How do I sum a total that contains identical amounts with sum distinct?

I have a view that takes information from 3 tables and sums a total column from one of the tables.  It works until the amount is identical, then it drops one of them.  If I change it by a penny, then of course, it works.  I have attached a picture of the code and the data table being used.  I don't know how to fix this problem.

Thanks for any insight into the problem.
2014-10-06-11-46-19.png
0
Karen Wilson
Asked:
Karen Wilson
1 Solution
 
AnthonyHamonCommented:
The problem appears to be caused by the SUM(DISTINCT(...)) clauses.  Why are you using this instead of a simple SUM(...) clause?
0
 
Karen WilsonAuthor Commented:
Because when I don't, it gives me the sum for the whole task order, versus just for that particular week and contract.
0
 
ste5anSenior DeveloperCommented:
Why are you joining LaborInput and TaskOrders by NASA_Contract and not by TaskOrder?
SUM DISTINCT means sum over all different values. E.g.

DECLARE @Sample TABLE 
(
	ID INT,
	Payload INT
);

INSERT INTO @Sample
VALUES	( 1, 1 ),
		( 2, 2 ),
		( 3, 2 ),
		( 4, 3 ),
		( 5, 3 ),
		( 6, 3 );

SELECT	SUM(S.Payload), 
		SUM(DISTINCT S.Payload)
FROM	@Sample S;

Open in new window


I really think this is wrong in your case. btw, one reason for the wrong result in your case is that IEEE floats are not precise, so you may see two different values on the grid but they may be the same.

Without further information, you may try a CTE or sub-query. E.g. something like:

WITH LaborInput AS
	(
		SELECT	L.NASA_Contract, 
				L.WeekEnding,
				SUM(L.WeeklyActuals) AS WeeklyCost,
				SUM(L.BurdenLabor) AS ReqLabor
		FROM	tblLaborInput L
		GROUP BY L.NASA_Contract, 
			L.WeekEnding
	)
	<TheRestOfYourQueryGoesHere>;

Open in new window


p.s. please include at least table DDL. It's really hard to help when we need to transcript from an image.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Karen WilsonAuthor Commented:
Sorry about that... I was going to add it and forgot.  

I am addressing your suggestion right now.  The code is about 4 years old and has just been added to versus taking a second look.  I'll let you know what I come up with.


SELECT        TOP (100) PERCENT dbo.tblNASAcontracts.Contract, dbo.tblLaborInput.WeekEnding, dbo.tblNASAcontracts.Rev, dbo.tblNASAcontracts.StartDate,
                         dbo.tblNASAcontracts.EndDate, SUM(dbo.tblLaborInput.BurdenLabor) AS RegLabor, SUM(dbo.tblLaborInput.OTBurdenLabor) AS OTLabor,
                         SUM(dbo.tblLaborInput.TravelBurden) AS Travel, SUM(dbo.tblLaborInput.WeeklyActuals) AS WeeklyCost
FROM            dbo.tblLaborInput INNER JOIN
                         dbo.tblTaskOrders ON dbo.tblLaborInput.NASAContract = dbo.tblTaskOrders.NASA_Contract RIGHT OUTER JOIN
                         dbo.tblNASAcontracts ON dbo.tblTaskOrders.Rev = dbo.tblNASAcontracts.Rev AND dbo.tblTaskOrders.NASA_Contract = dbo.tblNASAcontracts.Contract
GROUP BY dbo.tblNASAcontracts.Contract, dbo.tblLaborInput.WeekEnding, dbo.tblNASAcontracts.StartDate, dbo.tblNASAcontracts.EndDate, dbo.tblNASAcontracts.Rev
ORDER BY dbo.tblNASAcontracts.Contract, dbo.tblLaborInput.WeekEnding
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Without reading your T-SQL (sorry), I have an article out there on SQL Server Delete Duplicate Rows Solutions, that uses a CTE and a ROW_NUMBER to sort the rows by however you wish, without the same number for duplicate rows, then just delete all the non-ROW_NUMBER=1's.  

In the DELETE #1 section..

;with a as (
   SELECT flight_date, flight_number, dep, reg, 
      row_number() OVER (partition by flight_date, flight_number, dep, reg ORDER BY ISNULL(actual_out_tm, '23:59:59'), entered_dt) as row_number 
   FROM flights
)
DELETE FROM a WHERE row_number > 1

Open in new window

0
 
Karen WilsonAuthor Commented:
What I did was remove the task order table (it had become irrelevant in the view) and used a new column that was added last year to the contract table to provide the grouping I require to grab information.  I changed the group by to SUM and now everything works.  Friday, a big report is due so we'll see if all goes well!

Thanks!



Here is the code:

SELECT        TOP (100) PERCENT dbo.tblNASAcontracts.NASAapproved, dbo.tblNASAcontracts.Contract, dbo.tblLaborInput.WeekEnding, dbo.tblNASAcontracts.Rev,
                         dbo.tblNASAcontracts.StartDate, dbo.tblNASAcontracts.EndDate, SUM(dbo.tblLaborInput.BurdenLabor) AS RegLabor, SUM(dbo.tblLaborInput.OTBurdenLabor)
                         AS OTLabor, SUM(dbo.tblLaborInput.TravelBurden) AS Travel, SUM(dbo.tblLaborInput.WeeklyActuals) AS WeeklyCost
FROM            dbo.tblNASAcontracts LEFT OUTER JOIN
                         dbo.tblLaborInput ON dbo.tblNASAcontracts.Contract = dbo.tblLaborInput.NASAContract
GROUP BY dbo.tblNASAcontracts.Contract, dbo.tblLaborInput.WeekEnding, dbo.tblNASAcontracts.StartDate, dbo.tblNASAcontracts.EndDate, dbo.tblNASAcontracts.Rev,
                         dbo.tblNASAcontracts.NASAapproved
ORDER BY dbo.tblNASAcontracts.NASAapproved DESC
0
 
PortletPaulCommented:
no points, off topic

TOP (100) PERCENT ..... ORDER BY ....

it is usually very wasteful on system resources to order a view.
Ordering should be delayed until the very last moment.


(yes, there can be occasions when it is needed, but probably not here)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now