Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

getting duplicate value in my aggregate column??

Posted on 2011-09-14
13
Medium Priority
?
279 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
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 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

580 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