Solved

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

Posted on 2014-10-06
7
122 Views
Last Modified: 2014-10-07
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
Comment
Question by:Karen Wilson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 4

Expert Comment

by:AnthonyHamon
ID: 40364264
The problem appears to be caused by the SUM(DISTINCT(...)) clauses.  Why are you using this instead of a simple SUM(...) clause?
0
 

Author Comment

by:Karen Wilson
ID: 40364271
Because when I don't, it gives me the sum for the whole task order, versus just for that particular week and contract.
0
 
LVL 34

Accepted Solution

by:
ste5an earned 500 total points
ID: 40364328
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:Karen Wilson
ID: 40364347
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40364598
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
 

Author Closing Comment

by:Karen Wilson
ID: 40364764
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40365484
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

726 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