Solved

getting duplicate value in my aggregate column??

Posted on 2011-09-14
13
261 Views
Last Modified: 2012-05-12
The value for column [Clothing Order Qty] which is using SUM aggregate is bringing me back twice the value. I have narrowed it down to the INNER JOIN on DimFabric but I dont see why I would get back twice the value? Ive had this happen before but I forget what I did to remedy this. Thanks

SELECT  c.Season, c.[JA Fabric#], 0 AS [Fabric Order Qty],
SUM(c.CustomerOrderQty) AS [Clothing Order Qty], c.Category, d.Range
FROM         ClothingOrders c
INNER JOIN DimFabric d ON c.[JA Fabric#] = d.FabricBusinessKey
WHERE   c.Season ='195' and d.DivisionCode = '04'
and c.[JA Fabric#]='110350'
GROUP BY c.Season, c.[JA Fabric#], c.Category, d.Range
0
Comment
Question by:johnnyaction
[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
  • 6
  • 6
13 Comments
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 36537927
You probably have a case where c.[JA Fabrib#] (from ClothingOrders) is being found more than one time in the DimFabric d table.

To verify this run the following query:

select d.FabricBusinessKey, count(*)
from DimFabric d
group by d.FabricBusinessKey
having count(*) > 1

If this returns results, it means you need to add another field to your join criteria, or remove the potential duplicate row in your DimFabric
0
 
LVL 9

Expert Comment

by:DrewKjell
ID: 36537929
How many records return when you run the following query?
 
select *
from DimFabric
where FabricBusinessKey = '110350'
and DivisionCode = '04'

Open in new window

0
 
LVL 1

Author Comment

by:johnnyaction
ID: 36538058
This applies to both answers....

I only get one record back with the Season = 195.

Anything else I cuold do?
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 36538099
@JohnnyAction,

So the query I posted returned no results?

Thanks for confirming!
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 36538149
no there are 2 records (1 for Season 194 and 1 for Season 195). But the SUM(c.CustomerOrderQty) AS [Clothing Order Qty] in table ClothingOrders is 410 but with the join it is returning 820??
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 36538266
Not sure why it's duplicating but just in case it does have to do with the dups in your join table, check to see if changing up your query like this resolves the issue.


SELECT		
	c.Season
,	c.[JA Fabric#]
,	0 AS [Fabric Order Qty]
,	SUM(c.CustomerOrderQty) AS [Clothing Order Qty]
,	c.Category
,	d.Range


FROM         ClothingOrders c

INNER JOIN 
(
	SELECT FabricBusinessKey, MAX(Range) as Range
	from	DimFabric
	group by FabricBusinessKey
	
) d ON c.[JA Fabric#] = d.FabricBusinessKey

WHERE   c.Season ='195' and d.DivisionCode = '04'

and c.[JA Fabric#]='110350'

GROUP BY c.Season, c.[JA Fabric#], c.Category, d.Range

Open in new window

0
 
LVL 1

Author Comment

by:johnnyaction
ID: 36538609
Well that worked...This is my original query. I guess I would have to incorporate that within the query then? Note that vwFabric2 is a Select statement to DimFabric with a left join to another table which typically doesnt yield any rows. How wuold I incorporate what you have done within this query? Or do you have a simpler way of doing it after seeing this query? Thanks for your help!!!

SELECT  ClothingOrders.Season, ClothingOrders.[JA Fabric#], 0 AS [Fabric Order Qty], SUM(ClothingOrders.CustomerOrderQty) AS [Clothing Order Qty],
CASE WHEN ClothingOrders.Date <= '2011-10-01' THEN 'Spring 1 -- 10-1 to 10-31'  
WHEN ClothingOrders.Date BETWEEN '2011-11-01' AND '2011-11-30' THEN 'Spring 2 -- 11-1 to 11-30'
WHEN ClothingOrders.Date  BETWEEN '2011-12-01' AND '2011-12-31' THEN 'Spring 3 -- 12-1 to 12-31'
WHEN ClothingOrders.Date  BETWEEN '2012-01-01' AND '2012-01-31' THEN 'Spring 4 -- 1-1 to 1-31'
WHEN ClothingOrders.Date  BETWEEN '2012-02-01' AND '2012-02-28' THEN 'Spring 5 -- 2-1 to 2-28'
ELSE 'Spring 6 -- 3-1 to 3-31' END AS [Delivery Period],  ClothingOrders.Category, vwFabric2.Range,vwFabric2.CarryOver
FROM         ClothingOrders
INNER JOIN DimDate ON ClothingOrders.Date = DimDate.Date
INNER JOIN vwFabric2 ON ClothingOrders.[JA Fabric#] = vwFabric2.FabricBusinessKey
WHERE   ClothingOrders.Season ='195' and vwFabric2.DivisionCode = '04'
and ClothingOrders.[JA Fabric#]='110350'
GROUP BY ClothingOrders.Season, ClothingOrders.[JA Fabric#],
CASE WHEN ClothingOrders.Date <= '2011-10-01' THEN 'Spring 1 -- 10-1 to 10-31'  
WHEN ClothingOrders.Date BETWEEN '2011-11-01' AND '2011-11-30' THEN 'Spring 2 -- 11-1 to 11-30'
WHEN ClothingOrders.Date  BETWEEN '2011-12-01' AND '2011-12-31' THEN 'Spring 3 -- 12-1 to 12-31'
WHEN ClothingOrders.Date  BETWEEN '2012-01-01' AND '2012-01-31' THEN 'Spring 4 -- 1-1 to 1-31'
WHEN ClothingOrders.Date  BETWEEN '2012-02-01' AND '2012-02-28' THEN 'Spring 5 -- 2-1 to 2-28'
ELSE 'Spring 6 -- 3-1 to 3-31' END,
ClothingOrders.Category, vwFabric2.Range,vwFabric2.CarryOver
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 36538636
Well the easiest way to fix this issue is to make sure you don't have any duplicate FabricBusinessKey records in your DimFabric table.  

Is it possible to remove one of the duplicate FabricBusinessKey records in that table?  If so, your query will work fine just the way you had it.  

Even better, not sure what kind of system this is, but if FabricBusinessKey is supposed to be unique in that table, it might be a good idea to start discussing putting a unqiue constraint on that field in that table will all of the developers who use that table.

If it's not possible to remove that duplicate FabricBusinessKey, let me know and I'll take a look at your above posted code.
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 36538667
Its not a duplicate therefore not possible to remove. Thanks
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 36538734
The attached code will use the first instance of FabricBusinessKey in the vwFabric2 view basedon the order of Range.  The use of FabricBusinessKey may not represent a duplicate, but I'm betting that the same value exists in that table more than once in that field. (maybe with a different range/carryover)
;with FabricDim as
(
	select FabricBusinessKey, Range, CarryOver, ROW_NUMBER() over (partition by FabricBusinessKey order by Range) as Instance_Counter
	from vwFabric2
)	

SELECT  
	ClothingOrders.Season
,	ClothingOrders.[JA Fabric#]
,	0 AS [Fabric Order Qty]
,	SUM(ClothingOrders.CustomerOrderQty) AS [Clothing Order Qty]
,	CASE WHEN ClothingOrders.Date <= '2011-10-01' THEN 'Spring 1 -- 10-1 to 10-31'  
		WHEN ClothingOrders.Date BETWEEN '2011-11-01' AND '2011-11-30' THEN 'Spring 2 -- 11-1 to 11-30'
		WHEN ClothingOrders.Date  BETWEEN '2011-12-01' AND '2011-12-31' THEN 'Spring 3 -- 12-1 to 12-31'
		WHEN ClothingOrders.Date  BETWEEN '2012-01-01' AND '2012-01-31' THEN 'Spring 4 -- 1-1 to 1-31'
		WHEN ClothingOrders.Date  BETWEEN '2012-02-01' AND '2012-02-28' THEN 'Spring 5 -- 2-1 to 2-28'
		ELSE 'Spring 6 -- 3-1 to 3-31' 
	END AS [Delivery Period]
,	ClothingOrders.Category

,	FabricDIM.Range
,	FabricDIM.CarryOver

FROM         ClothingOrders

INNER JOIN DimDate ON ClothingOrders.Date = DimDate.Date

INNER JOIN FabricDIM ON ClothingOrders.[JA Fabric#] = FabricDIM.FabricBusinessKey and FabricDim.Instance_Counter = 1
						
WHERE   ClothingOrders.Season ='195' and vwFabric2.DivisionCode = '04'

and ClothingOrders.[JA Fabric#]='110350'

GROUP BY ClothingOrders.Season, ClothingOrders.[JA Fabric#],

CASE WHEN ClothingOrders.Date <= '2011-10-01' THEN 'Spring 1 -- 10-1 to 10-31'  
WHEN ClothingOrders.Date BETWEEN '2011-11-01' AND '2011-11-30' THEN 'Spring 2 -- 11-1 to 11-30'
WHEN ClothingOrders.Date  BETWEEN '2011-12-01' AND '2011-12-31' THEN 'Spring 3 -- 12-1 to 12-31'
WHEN ClothingOrders.Date  BETWEEN '2012-01-01' AND '2012-01-31' THEN 'Spring 4 -- 1-1 to 1-31'
WHEN ClothingOrders.Date  BETWEEN '2012-02-01' AND '2012-02-28' THEN 'Spring 5 -- 2-1 to 2-28'
ELSE 'Spring 6 -- 3-1 to 3-31' END,

ClothingOrders.Category, FabricDIM.Range, FabricDIM.CarryOver

Open in new window

0
 
LVL 14

Accepted Solution

by:
Christopher Gordon earned 500 total points
ID: 36538961
Sorry noticed a bug in my code.  Didn't take into account the field DivisionCode.
;with FabricDim as
(
	select FabricBusinessKey, Range, CarryOver, DivisionCode, ROW_NUMBER() over (partition by FabricBusinessKey order by Range) as Instance_Counter
	from vwFabric2
)	

SELECT  
	ClothingOrders.Season
,	ClothingOrders.[JA Fabric#]
,	0 AS [Fabric Order Qty]
,	SUM(ClothingOrders.CustomerOrderQty) AS [Clothing Order Qty]
,	CASE WHEN ClothingOrders.Date <= '2011-10-01' THEN 'Spring 1 -- 10-1 to 10-31'  
		WHEN ClothingOrders.Date BETWEEN '2011-11-01' AND '2011-11-30' THEN 'Spring 2 -- 11-1 to 11-30'
		WHEN ClothingOrders.Date  BETWEEN '2011-12-01' AND '2011-12-31' THEN 'Spring 3 -- 12-1 to 12-31'
		WHEN ClothingOrders.Date  BETWEEN '2012-01-01' AND '2012-01-31' THEN 'Spring 4 -- 1-1 to 1-31'
		WHEN ClothingOrders.Date  BETWEEN '2012-02-01' AND '2012-02-28' THEN 'Spring 5 -- 2-1 to 2-28'
		ELSE 'Spring 6 -- 3-1 to 3-31' 
	END AS [Delivery Period]
,	ClothingOrders.Category

,	FabricDIM.Range
,	FabricDIM.CarryOver

FROM         ClothingOrders

INNER JOIN DimDate ON ClothingOrders.Date = DimDate.Date

INNER JOIN FabricDIM ON ClothingOrders.[JA Fabric#] = FabricDIM.FabricBusinessKey and FabricDim.Instance_Counter = 1
						
WHERE   ClothingOrders.Season ='195' and FabricDIM.DivisionCode = '04'

and ClothingOrders.[JA Fabric#]='110350'

GROUP BY ClothingOrders.Season, ClothingOrders.[JA Fabric#],

CASE WHEN ClothingOrders.Date <= '2011-10-01' THEN 'Spring 1 -- 10-1 to 10-31'  
WHEN ClothingOrders.Date BETWEEN '2011-11-01' AND '2011-11-30' THEN 'Spring 2 -- 11-1 to 11-30'
WHEN ClothingOrders.Date  BETWEEN '2011-12-01' AND '2011-12-31' THEN 'Spring 3 -- 12-1 to 12-31'
WHEN ClothingOrders.Date  BETWEEN '2012-01-01' AND '2012-01-31' THEN 'Spring 4 -- 1-1 to 1-31'
WHEN ClothingOrders.Date  BETWEEN '2012-02-01' AND '2012-02-28' THEN 'Spring 5 -- 2-1 to 2-28'
ELSE 'Spring 6 -- 3-1 to 3-31' END,

ClothingOrders.Category, FabricDIM.Range, FabricDIM.CarryOver

Open in new window

0
 
LVL 1

Author Comment

by:johnnyaction
ID: 36539526
ok thanks I will have to check it in the morning..I will let you know how it comes out..Thanks
0
 
LVL 1

Author Closing Comment

by:johnnyaction
ID: 36543361
worked like a charm..thanks for your help
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

751 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