Solved

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

Posted on 2014-10-06
7
121 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 33

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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 

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 65

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 48

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

730 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