Solved

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

Posted on 2014-10-06
7
120 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
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

840 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