locked
How to merge two SQL rows (same item ID) with the SUM() qty but show only the last row's info? RRS feed

  • Question

  • I was trying to use DISTINCT to show unique record, but not every column has same value.

    Is there a way to merge my Count column's value of two rows that have the same UPC (item ID) but only show the 2nd (latest) info of the rest of the column like this?

    Because my 'Unit Cost' has two different value, my DISTINCT function still produces two rows instead of just one.

    If I just use TOP 1, then my Count value will not SUM both qty.

    Would the OVER (Partition) or maybe UNION function work?

    Thanks.

    This is my current SQL query:

    SELECT DISTINCT TOP 10
    	--1.UPC
    	CASE WHEN [UPC] IS NULL THEN ( 'Grand Total: ' ) + ' (' +(CONVERT(varchar(10), COUNT(DISTINCT [UPC]))) + ' unique items)'
    	ELSE [UPC]
    	END AS [UPC]
    
    	--2.ItemCode
    	,	CASE WHEN [ItemCode] IS NULL THEN ''
    		ELSE [ItemCode] 
    		END AS [Item Code]
    			
    	--3.Vendor
    	,ISNULL(CONVERT(varchar(30),[Vendor]),'') [Vendor#]
    		
    	--4.MixMatch
    	,	CASE WHEN [MixMatch] IS NULL THEN ''
    		ELSE [MixMatch] 
    		END AS [Mix Match]
    
    	--5.Description
    	,	CASE WHEN [Description] IS NULL THEN ''
    		ELSE [Description] 
    		END AS [Description]
    
    	--6.CaseSize
    	,ISNULL(CONVERT(varchar(5),[CaseSize]),'') [Case Size]
    
    	--7.Size
    	,ISNULL(CONVERT(varchar(10),[Size]),'') [Size]
    
    	--8.Measure
    	,	CASE WHEN [Measure] IS NULL THEN ''
    		ELSE [Measure] 
    		END AS [Measure]
    
    	--9.Date Listed
    	,ISNULL(CONVERT(varchar(20),[CreationDate]),'') [Date Listed]
    
    	--10.Case Cost
    	--,ISNULL(CONVERT(varchar(20),[CaseCost]),'') [Case Cost]
    	,ISNULL( '$' + CONVERT(varchar(20),[CaseCost]),'') [Case Cost]
    
    	--11.Unit Cost
    	,ISNULL( '$' + CONVERT(varchar(20),[UnitCost]),'') [Unit Cost]
    
    	--14.Count
    	,CONVERT(int,ROUND(SUM([QtySold]),0)) AS [Count]
    
    FROM [JS_Data].[dbo].[View_ItemMovement_TopSellers]
    WHERE (UPC = '82983500006')
           AND (SaleDate BETWEEN '2013-05-01' AND '2014-05-01')
    
    GROUP BY 
    	GROUPING SETS 
    	(
    		([UPC], [ItemCode], [Vendor], [MixMatch], [Description], [CaseSize], [Size], [Measure], [CreationDate], [CaseCost], [UnitCost])
    		,()		
    	)
    ORDER BY UPC
    
    
    
    

    Friday, July 11, 2014 10:07 PM

Answers

  • Sorry, forgot to include the code:

    --SELECT DISTINCT TOP 11
    SELECT
    	--1.UPC
    	CASE WHEN [UPC] IS NULL THEN ( 'Grand Total: ' ) + ' (' +(CONVERT(varchar(10), COUNT(DISTINCT [UPC]))) + ' unique items)'
    	ELSE [UPC]
    	END AS [UPC]
    
    	--2.ItemCode
    	,	CASE WHEN [ItemCode] IS NULL THEN ''
    		ELSE [ItemCode] 
    		END AS [Item Code]
    			
    	--3.Vendor
    	,ISNULL(CONVERT(varchar(30),[Vendor]),'') [Vendor#]
    		
    	--4.MixMatch
    	,	CASE WHEN [MixMatch] IS NULL THEN ''
    		ELSE [MixMatch] 
    		END AS [Mix Match]
    
    	--5.Description
    	,	CASE WHEN [Description] IS NULL THEN ''
    		ELSE [Description] 
    		END AS [Description]
    
    	--6.CaseSize
    	,ISNULL(CONVERT(varchar(5),[CaseSize]),'') [Case Size]
    
    	--7.Size
    	,ISNULL(CONVERT(varchar(10),[Size]),'') [Size]
    
    	--8.Measure
    	,	CASE WHEN [Measure] IS NULL THEN ''
    		ELSE [Measure] 
    		END AS [Measure]
    
    	--9.Date Listed
    	,ISNULL(CONVERT(varchar(20),[CreationDate]),'') [Date Listed]
    
    	--10.Case Cost
    	--,ISNULL(CONVERT(varchar(20),[CaseCost]),'') [Case Cost]
    	--,ISNULL( '$' + CONVERT(varchar(20),[CaseCost]),'') [Case Cost]
    	,ISNULL( '$' + CONVERT(varchar(20),[CaseCost]),'') [Case Cost]
    
    	--11.Unit Cost
    	,ISNULL( '$' + CONVERT(varchar(20),[UnitCost]),'') [Unit Cost]
    
    	--14.Count
    	,CONVERT(int,ROUND(SUM([QtySold]),0)) AS [Count]
    
    
    FROM 
    (
    	select top 10
    	[UPC],[ItemCode],[Vendor],[MixMatch],[Description],[CaseSize],[Size],[Measure],[CreationDate]
    	,max([CaseCost]) as [CaseCost],max([UnitCost]) as [UnitCost],sum([QtySold]) as [QtySold]
    	from [JS_Data].[dbo].[View_ItemMovement_TopSellers]
    	WHERE (UPC BETWEEN '82983500000' AND '82983599999')
    		   AND (SaleDate BETWEEN '2013-05-01' AND '2014-05-01')
    	GROUP BY [UPC], [ItemCode], [Vendor], [MixMatch], [Description], [CaseSize], [Size], [Measure], [CreationDate]
    ) a
    
    GROUP BY
    	GROUPING SETS 
    	(
    		([UPC], [ItemCode], [Vendor], [MixMatch], [Description], [CaseSize], [Size], [Measure], [CreationDate], [CaseCost], [UnitCost])
    		,()		
    	)
    ORDER BY [Count] DESC


    The Count of the first 9 rows before was correct, but this correct way still confuses me how come the Count now skips some UPC rows.

    The result for Count should be like this:

    I think there's still something wrong or missing with my GROUPING SETS?


    Are you want to sort by last count column desc, you can't place order by with in derived table(subquery) you can use row_number()

    Also note the will be always one row extra for grant total

    SELECT 
    	--1.UPC
    	CASE WHEN [UPC] IS NULL THEN ( 'Grand Total: ' ) + ' (' +(CONVERT(varchar(10), COUNT(DISTINCT [UPC]))) + ' unique items)'
    	ELSE [UPC]
    	END AS [UPC]
    	--2.ItemCode
    	,	CASE WHEN [ItemCode] IS NULL THEN ''
    		ELSE [ItemCode] 
    		END AS [Item Code]
    			
    	--3.Vendor
    	,ISNULL(CONVERT(varchar(30),[Vendor]),'') [Vendor#]
    		
    	--4.MixMatch
    	,	CASE WHEN [MixMatch] IS NULL THEN ''
    		ELSE [MixMatch] 
    		END AS [Mix Match]
    
    	--5.Description
    	,	CASE WHEN [Description] IS NULL THEN ''
    		ELSE [Description] 
    		END AS [Description]
    
    	--6.CaseSize
    	,ISNULL(CONVERT(varchar(5),[CaseSize]),'') [Case Size]
    
    	--7.Size
    	,ISNULL(CONVERT(varchar(10),[Size]),'') [Size]
    
    	--8.Measure
    	,	CASE WHEN [Measure] IS NULL THEN ''
    		ELSE [Measure] 
    		END AS [Measure]
    
    	--9.Date Listed
    	,ISNULL(CONVERT(varchar(20),[CreationDate]),'') [Date Listed]
    
    	--10.Case Cost
    	--,ISNULL(CONVERT(varchar(20),[CaseCost]),'') [Case Cost]
    	,ISNULL( '$' + CONVERT(varchar(20),[CaseCost]),'') [Case Cost]
    
    	--11.Unit Cost
    	,ISNULL( '$' + CONVERT(varchar(20),[UnitCost]),'') [Unit Cost]
    
    	--14.Count
    	,CONVERT(int,ROUND(SUM([QtySold]),0)) AS [Count]
    
    FROM 
    (select ROW_NUMBER() over(order by sum([QtySold]) desc) as rno
    [UPC],[Item Code],[Mix Match],[Description],[CaseSize],[Size],[Measure],[CreationDate],[CaseCost],max([UnitCost]) as [UnitCost],sum([QtySold]) as [QtySold]
    [JS_Data].[dbo].[View_ItemMovement_TopSellers]
    WHERE (UPC = '82983500006')
           AND (SaleDate BETWEEN '2013-05-01' AND '2014-05-01')
    group by 
    GROUP BY [UPC], [ItemCode], [Vendor], [MixMatch], [Description], [CaseSize], [Size], [Measure], [CreationDate], [CaseCost]) a
    where rno <= 10
    	GROUPING SETS 
    	(
    		([UPC], [ItemCode], [Vendor], [MixMatch], [Description], [CaseSize], [Size], [Measure], [CreationDate], [CaseCost], [UnitCost])
    		,()		
    	)
    ORDER BY rno


    • Edited by SaravanaC Saturday, July 12, 2014 7:47 AM
    • Proposed as answer by Ioana Vasilescu Saturday, July 12, 2014 8:18 AM
    • Marked as answer by dchencm Saturday, July 12, 2014 10:37 PM
    Saturday, July 12, 2014 7:44 AM

All replies

  • i see different in  unitcost, try below instead of unitcost

    --11.Unit Cost
    	,ISNULL( '$' + CONVERT(varchar(20),max([UnitCost])),'') [Unit Cost]

    And remove unit cost from group by 

    Thanks

    Saravana Kumar C



    • Edited by SaravanaC Friday, July 11, 2014 10:50 PM
    • Marked as answer by dchencm Friday, July 11, 2014 10:57 PM
    • Unmarked as answer by dchencm Saturday, July 12, 2014 5:15 AM
    Friday, July 11, 2014 10:49 PM
  • Thanks Saravana,

    It works, but now have another related problem.
    Because i have GROUP BY GROUPING SETS, even if I SELECT TOP 10, it only SELECT TOP 9.
    and the COUNT's total would also be different.


    but if I select DISTINCT TOP 11, it does display 10 correct UPC#, but the Grand Total of the Count field is still the same, which I'm not sure where that number actually comes from.

    And my counting of 10 unique UPCs somehow says 32 unique items?

    Saturday, July 12, 2014 4:52 AM
  • Thanks Saravana,

    It works, but now have another related problem.
    Because i have GROUP BY GROUPING SETS, even if I SELECT TOP 10, it only SELECT TOP 9.
    and the COUNT's total would also be different.


    but if I select DISTINCT TOP 11, it does display 10 correct UPC#, but the Grand Total of the Count field is still the same, which I'm not sure where that number actually comes from.

    And my counting of 10 unique UPCs somehow says 32 unique items?

    Hi

             please make sure unitcost is removed in grouping clause

    And the Grant Total give the unique count of UPC, but the last column count is giving you sum of all (SUM([QtySold]) off course  they should be different

    it is selecting only 10 unique UPC, because it is show in the top after grouping

    if you want to calculate only the top 10 or top 11, you need to derive the calculation from derived table which apply your count top like beow

    SELECT 
    	--1.UPC
    	CASE WHEN [UPC] IS NULL THEN ( 'Grand Total: ' ) + ' (' +(CONVERT(varchar(10), COUNT(DISTINCT [UPC]))) + ' unique items)'
    	ELSE [UPC]
    	END AS [UPC]
    	--2.ItemCode
    	,	CASE WHEN [ItemCode] IS NULL THEN ''
    		ELSE [ItemCode] 
    		END AS [Item Code]
    			
    	--3.Vendor
    	,ISNULL(CONVERT(varchar(30),[Vendor]),'') [Vendor#]
    		
    	--4.MixMatch
    	,	CASE WHEN [MixMatch] IS NULL THEN ''
    		ELSE [MixMatch] 
    		END AS [Mix Match]
    
    	--5.Description
    	,	CASE WHEN [Description] IS NULL THEN ''
    		ELSE [Description] 
    		END AS [Description]
    
    	--6.CaseSize
    	,ISNULL(CONVERT(varchar(5),[CaseSize]),'') [Case Size]
    
    	--7.Size
    	,ISNULL(CONVERT(varchar(10),[Size]),'') [Size]
    
    	--8.Measure
    	,	CASE WHEN [Measure] IS NULL THEN ''
    		ELSE [Measure] 
    		END AS [Measure]
    
    	--9.Date Listed
    	,ISNULL(CONVERT(varchar(20),[CreationDate]),'') [Date Listed]
    
    	--10.Case Cost
    	--,ISNULL(CONVERT(varchar(20),[CaseCost]),'') [Case Cost]
    	,ISNULL( '$' + CONVERT(varchar(20),[CaseCost]),'') [Case Cost]
    
    	--11.Unit Cost
    	,ISNULL( '$' + CONVERT(varchar(20),[UnitCost]),'') [Unit Cost]
    
    	--14.Count
    	,CONVERT(int,ROUND(SUM([QtySold]),0)) AS [Count]
    
    FROM 
    (select top 10
    [UPC],[Item Code],[Mix Match],[Description],[CaseSize],[Size],[Measure],[CreationDate],[CaseCost],max([UnitCost]) as [UnitCost],sum([QtySold]) as [QtySold]
    [JS_Data].[dbo].[View_ItemMovement_TopSellers]
    WHERE (UPC = '82983500006')
           AND (SaleDate BETWEEN '2013-05-01' AND '2014-05-01')
    group by 
    GROUP BY [UPC], [ItemCode], [Vendor], [MixMatch], [Description], [CaseSize], [Size], [Measure], [CreationDate], [CaseCost]) a
    	GROUPING SETS 
    	(
    		([UPC], [ItemCode], [Vendor], [MixMatch], [Description], [CaseSize], [Size], [Measure], [CreationDate], [CaseCost], [UnitCost])
    		,()		
    	)
    ORDER BY UPC


    Without DDL and DML we have no idea what is going on, we just had a guess and reply what is asked for ...

    • Edited by SaravanaC Saturday, July 12, 2014 5:33 AM
    Saturday, July 12, 2014 5:29 AM
  • Sorry, forgot to include the code:

    --SELECT DISTINCT TOP 11
    SELECT
    	--1.UPC
    	CASE WHEN [UPC] IS NULL THEN ( 'Grand Total: ' ) + ' (' +(CONVERT(varchar(10), COUNT(DISTINCT [UPC]))) + ' unique items)'
    	ELSE [UPC]
    	END AS [UPC]
    
    	--2.ItemCode
    	,	CASE WHEN [ItemCode] IS NULL THEN ''
    		ELSE [ItemCode] 
    		END AS [Item Code]
    			
    	--3.Vendor
    	,ISNULL(CONVERT(varchar(30),[Vendor]),'') [Vendor#]
    		
    	--4.MixMatch
    	,	CASE WHEN [MixMatch] IS NULL THEN ''
    		ELSE [MixMatch] 
    		END AS [Mix Match]
    
    	--5.Description
    	,	CASE WHEN [Description] IS NULL THEN ''
    		ELSE [Description] 
    		END AS [Description]
    
    	--6.CaseSize
    	,ISNULL(CONVERT(varchar(5),[CaseSize]),'') [Case Size]
    
    	--7.Size
    	,ISNULL(CONVERT(varchar(10),[Size]),'') [Size]
    
    	--8.Measure
    	,	CASE WHEN [Measure] IS NULL THEN ''
    		ELSE [Measure] 
    		END AS [Measure]
    
    	--9.Date Listed
    	,ISNULL(CONVERT(varchar(20),[CreationDate]),'') [Date Listed]
    
    	--10.Case Cost
    	--,ISNULL(CONVERT(varchar(20),[CaseCost]),'') [Case Cost]
    	--,ISNULL( '$' + CONVERT(varchar(20),[CaseCost]),'') [Case Cost]
    	,ISNULL( '$' + CONVERT(varchar(20),[CaseCost]),'') [Case Cost]
    
    	--11.Unit Cost
    	,ISNULL( '$' + CONVERT(varchar(20),[UnitCost]),'') [Unit Cost]
    
    	--14.Count
    	,CONVERT(int,ROUND(SUM([QtySold]),0)) AS [Count]
    
    
    FROM 
    (
    	select top 10
    	[UPC],[ItemCode],[Vendor],[MixMatch],[Description],[CaseSize],[Size],[Measure],[CreationDate]
    	,max([CaseCost]) as [CaseCost],max([UnitCost]) as [UnitCost],sum([QtySold]) as [QtySold]
    	from [JS_Data].[dbo].[View_ItemMovement_TopSellers]
    	WHERE (UPC BETWEEN '82983500000' AND '82983599999')
    		   AND (SaleDate BETWEEN '2013-05-01' AND '2014-05-01')
    	GROUP BY [UPC], [ItemCode], [Vendor], [MixMatch], [Description], [CaseSize], [Size], [Measure], [CreationDate]
    ) a
    
    GROUP BY
    	GROUPING SETS 
    	(
    		([UPC], [ItemCode], [Vendor], [MixMatch], [Description], [CaseSize], [Size], [Measure], [CreationDate], [CaseCost], [UnitCost])
    		,()		
    	)
    ORDER BY [Count] DESC


    The Count of the first 9 rows before was correct, but this correct way still confuses me how come the Count now skips some UPC rows.

    The result for Count should be like this:

    I think there's still something wrong or missing with my GROUPING SETS?


    • Edited by dchencm Saturday, July 12, 2014 6:24 AM wrong pic
    Saturday, July 12, 2014 6:21 AM
  • Sorry, forgot to include the code:

    --SELECT DISTINCT TOP 11
    SELECT
    	--1.UPC
    	CASE WHEN [UPC] IS NULL THEN ( 'Grand Total: ' ) + ' (' +(CONVERT(varchar(10), COUNT(DISTINCT [UPC]))) + ' unique items)'
    	ELSE [UPC]
    	END AS [UPC]
    
    	--2.ItemCode
    	,	CASE WHEN [ItemCode] IS NULL THEN ''
    		ELSE [ItemCode] 
    		END AS [Item Code]
    			
    	--3.Vendor
    	,ISNULL(CONVERT(varchar(30),[Vendor]),'') [Vendor#]
    		
    	--4.MixMatch
    	,	CASE WHEN [MixMatch] IS NULL THEN ''
    		ELSE [MixMatch] 
    		END AS [Mix Match]
    
    	--5.Description
    	,	CASE WHEN [Description] IS NULL THEN ''
    		ELSE [Description] 
    		END AS [Description]
    
    	--6.CaseSize
    	,ISNULL(CONVERT(varchar(5),[CaseSize]),'') [Case Size]
    
    	--7.Size
    	,ISNULL(CONVERT(varchar(10),[Size]),'') [Size]
    
    	--8.Measure
    	,	CASE WHEN [Measure] IS NULL THEN ''
    		ELSE [Measure] 
    		END AS [Measure]
    
    	--9.Date Listed
    	,ISNULL(CONVERT(varchar(20),[CreationDate]),'') [Date Listed]
    
    	--10.Case Cost
    	--,ISNULL(CONVERT(varchar(20),[CaseCost]),'') [Case Cost]
    	--,ISNULL( '$' + CONVERT(varchar(20),[CaseCost]),'') [Case Cost]
    	,ISNULL( '$' + CONVERT(varchar(20),[CaseCost]),'') [Case Cost]
    
    	--11.Unit Cost
    	,ISNULL( '$' + CONVERT(varchar(20),[UnitCost]),'') [Unit Cost]
    
    	--14.Count
    	,CONVERT(int,ROUND(SUM([QtySold]),0)) AS [Count]
    
    
    FROM 
    (
    	select top 10
    	[UPC],[ItemCode],[Vendor],[MixMatch],[Description],[CaseSize],[Size],[Measure],[CreationDate]
    	,max([CaseCost]) as [CaseCost],max([UnitCost]) as [UnitCost],sum([QtySold]) as [QtySold]
    	from [JS_Data].[dbo].[View_ItemMovement_TopSellers]
    	WHERE (UPC BETWEEN '82983500000' AND '82983599999')
    		   AND (SaleDate BETWEEN '2013-05-01' AND '2014-05-01')
    	GROUP BY [UPC], [ItemCode], [Vendor], [MixMatch], [Description], [CaseSize], [Size], [Measure], [CreationDate]
    ) a
    
    GROUP BY
    	GROUPING SETS 
    	(
    		([UPC], [ItemCode], [Vendor], [MixMatch], [Description], [CaseSize], [Size], [Measure], [CreationDate], [CaseCost], [UnitCost])
    		,()		
    	)
    ORDER BY [Count] DESC


    The Count of the first 9 rows before was correct, but this correct way still confuses me how come the Count now skips some UPC rows.

    The result for Count should be like this:

    I think there's still something wrong or missing with my GROUPING SETS?


    Are you want to sort by last count column desc, you can't place order by with in derived table(subquery) you can use row_number()

    Also note the will be always one row extra for grant total

    SELECT 
    	--1.UPC
    	CASE WHEN [UPC] IS NULL THEN ( 'Grand Total: ' ) + ' (' +(CONVERT(varchar(10), COUNT(DISTINCT [UPC]))) + ' unique items)'
    	ELSE [UPC]
    	END AS [UPC]
    	--2.ItemCode
    	,	CASE WHEN [ItemCode] IS NULL THEN ''
    		ELSE [ItemCode] 
    		END AS [Item Code]
    			
    	--3.Vendor
    	,ISNULL(CONVERT(varchar(30),[Vendor]),'') [Vendor#]
    		
    	--4.MixMatch
    	,	CASE WHEN [MixMatch] IS NULL THEN ''
    		ELSE [MixMatch] 
    		END AS [Mix Match]
    
    	--5.Description
    	,	CASE WHEN [Description] IS NULL THEN ''
    		ELSE [Description] 
    		END AS [Description]
    
    	--6.CaseSize
    	,ISNULL(CONVERT(varchar(5),[CaseSize]),'') [Case Size]
    
    	--7.Size
    	,ISNULL(CONVERT(varchar(10),[Size]),'') [Size]
    
    	--8.Measure
    	,	CASE WHEN [Measure] IS NULL THEN ''
    		ELSE [Measure] 
    		END AS [Measure]
    
    	--9.Date Listed
    	,ISNULL(CONVERT(varchar(20),[CreationDate]),'') [Date Listed]
    
    	--10.Case Cost
    	--,ISNULL(CONVERT(varchar(20),[CaseCost]),'') [Case Cost]
    	,ISNULL( '$' + CONVERT(varchar(20),[CaseCost]),'') [Case Cost]
    
    	--11.Unit Cost
    	,ISNULL( '$' + CONVERT(varchar(20),[UnitCost]),'') [Unit Cost]
    
    	--14.Count
    	,CONVERT(int,ROUND(SUM([QtySold]),0)) AS [Count]
    
    FROM 
    (select ROW_NUMBER() over(order by sum([QtySold]) desc) as rno
    [UPC],[Item Code],[Mix Match],[Description],[CaseSize],[Size],[Measure],[CreationDate],[CaseCost],max([UnitCost]) as [UnitCost],sum([QtySold]) as [QtySold]
    [JS_Data].[dbo].[View_ItemMovement_TopSellers]
    WHERE (UPC = '82983500006')
           AND (SaleDate BETWEEN '2013-05-01' AND '2014-05-01')
    group by 
    GROUP BY [UPC], [ItemCode], [Vendor], [MixMatch], [Description], [CaseSize], [Size], [Measure], [CreationDate], [CaseCost]) a
    where rno <= 10
    	GROUPING SETS 
    	(
    		([UPC], [ItemCode], [Vendor], [MixMatch], [Description], [CaseSize], [Size], [Measure], [CreationDate], [CaseCost], [UnitCost])
    		,()		
    	)
    ORDER BY rno


    • Edited by SaravanaC Saturday, July 12, 2014 7:47 AM
    • Proposed as answer by Ioana Vasilescu Saturday, July 12, 2014 8:18 AM
    • Marked as answer by dchencm Saturday, July 12, 2014 10:37 PM
    Saturday, July 12, 2014 7:44 AM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. You have no idea and do not even know reserved SQL words. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    >> I was trying to use DISTINCT to show unique record [sic: rows are not records], but not every column has same value. Is there a way to merge my Count column's value of two rows that have the same upc (item ID) but only show the 2nd (latest) info of the rest of the column like this? <<

    How can we answer without sample data or DDL? Why do you have a UPC and an item_id if they are the same entity? However, it was nice to find someone who knows what the UPC is. Have you ever heard of the information Principle? It is one of Codd's 12 rules for RDBMS. It says that all relationships (like which row to use in your non-query) are modeled with scalar values in columns of tables because there is no physical ordering. What did you use to pick a row? 

    In the case of a history, we need a (start_date, end_date) pair to tell us when that row was in effect. Again, this is a basic idiom in RDBMS. 

    We do not do display formatting in a query. That was COBOL in the 1950's, not any tiered architecture today. We also have used the old Sybase CONVERT() for a decade at least; we have had the ANSI/ISO CAST() about that long. 

    Would you please follow Netiquette and try again? Without the silly pictures.



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, July 12, 2014 12:26 PM