Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

getting duplicate value in my aggregate column??

Posted on 2011-09-14
13
Medium Priority
?
276 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

886 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