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
Karen WilsonProgrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 WilsonProgrammerAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Karen WilsonProgrammerAuthor 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 WilsonProgrammerAuthor 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
PortletPaulfreelancerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.