Solved

getting duplicate value in my aggregate column??

Posted on 2011-09-14
13
214 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
  • 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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

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.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now