Solved

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

Posted on 2014-10-06
122 Views
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.
0
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

LVL 4

Expert Comment

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

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

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,
);

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

FROM	@Sample S;
``````

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>;
``````

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

Author Comment

ID: 40364347

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

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
``````
0

Author Closing Comment

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

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

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.
###### Suggested Courses
Course of the Month6 days, 7 hours left to enroll