Solved

getting duplicate value in my aggregate column??

Posted on 2011-09-14
13
240 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
insert wont work in SQL 14 22
T-SQL Default value in Select? 5 29
Syntax Issue with SSIS module 26 59
get_systemdrive info from tsql? 1 6
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

825 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