SQL merge duplicate rows using group by

user_8914959 0
Viewed 1037

I have a table with values such as this;

ACC     SALES LY   SALES TY      YEAR
-------------------------------------
B0022      0           15        2017
B0022      22          0         2016
AA000      0           12        2017
AA000      6           0         2016

I'm looking to create a view that'll merge rows with the same ACC. So What I'm looking for is this;

ACC     SALES LY   SALES TY      YEAR
--------------------------------------
B0022      22          15        EMPTY
AA000       6          12        EMPTY

I have looked at GROUP BY clauses, over(Partition by) and I just cannot get it to work. This only shows part of my current table/view.

The full view code is here: 

SELECT DISTINCT TOP (100) PERCENT 
    dbo.[Sales By SKU Units].[Customer Account Number], 
    CASE 
       WHEN [Sales By SKU Units].[Customer Account Number] = 'S0040' THEN 'H'
       WHEN [Sales By SKU Units].[Customer Account Number] = 'Z0004' THEN 'E'
       WHEN [Sales By SKU Units].[Customer Account Number] = 'N0014' THEN 'G' 
       WHEN [Sales By SKU Units].[Customer Account Number] = 'B0022' THEN 'B'
       WHEN [Sales By SKU Units].[Customer Account Number] = 'A0097' THEN 'F' 
       WHEN [Sales By SKU Units].[Customer Account Number] = 'H0085' THEN 'F'
       WHEN [Sales By SKU Units].[Customer Account Number] = 'A0044' THEN 'A' 
       WHEN [Sales By SKU Units].[Customer Account Number] = 'S0482' THEN 'W'
    END AS CustomerName, 
    dbo.[Sales By SKU Units].SKU, 
    dbo.RangeLists.Column1 AS Status, 
    CASE 
       WHEN [Sales By SKU Value].[W/H Stock] IS NULL THEN '0' 
       WHEN [Sales By SKU Value].[W/H Stock] = '' THEN '0' 
       WHEN [Sales By SKU Value].[W/H Stock] IS NOT NULL THEN [Sales By SKU Value].[W/H Stock] 
    END AS [Warehouse Stock], 
    CASE 
       WHEN [Sales By SKU Value].[Store Stock] IS NULL THEN '0' 
       WHEN [Sales By SKU Value].[Store Stock] = '' THEN '0' 
       WHEN [Sales By SKU Value].[Store Stock] IS NOT NULL THEN [Sales By SKU Value].[Store Stock] 
    END AS [Store Stock], 
    CASE 
        WHEN [Sales By SKU Value].[On Order] IS NULL THEN '0' 
        WHEN [Sales By SKU Value].[On Order] = '' THEN '0' 
        WHEN [Sales By SKU Value].[On Order] IS NOT NULL THEN [Sales By SKU Value].[On Order] 
    END AS [On Order], 
    CASE 
        WHEN [Sales By SKU Value].[Total Stock] IS NULL THEN '0' 
        WHEN [Sales By SKU Value].[Total Stock] = '' THEN '0' 
        WHEN [Sales By SKU Value].[Total Stock] IS NOT NULL THEN [Sales By SKU Value].[Total Stock] 
    END AS [Total Stock], 
    CASE 
       WHEN [Sales By SKU Value].[No Of Stores] IS NULL THEN '0' 
       WHEN [Sales By SKU Value].[No Of Stores] = '' THEN '0' 
       WHEN [Sales By SKU Value].[No Of Stores] IS NOT NULL THEN [Sales By SKU Value].[No Of Stores] 
    END AS [Number of Stores], 
    CASE 
       WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'jan' THEN [Sales By SKU Units].[Jan] 
       WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'feb' THEN [Sales By SKU Units].[Feb] 
       WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'mar' THEN [Sales By SKU Units].[March] 
       WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'apr' THEN [Sales By SKU Units].[April] 
       WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'may' THEN [Sales By SKU Units].[May] 
       WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'jun' THEN [Sales By SKU Units].[June] 
       WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'jul' THEN [Sales By SKU Units].[July] 
       WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'aug' THEN [Sales By SKU Units].[August] 
       WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'sep' THEN [Sales By SKU Units].[September] 
       WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'oct' THEN [Sales By SKU Units].[October] 
       WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'nov' THEN [Sales By SKU Units].[November] 
       WHEN LEFT(datename(month, DATEADD(MM, - 1, getdate())), 3) = 'dec' THEN [Sales By SKU Units].[December] 
       ELSE '0' 
    END AS [Sales Last Month], 
    dbo.RangeLists.pnum AS Model, 
    CASE 
        WHEN [Sales By SKU Units].[Year] = '2016' 
           THEN ISNULL([Sales By SKU Units].[Jan], 0) + ISNULL([Sales By SKU Units].[Feb], 0) + 
                ISNULL([Sales By SKU Units].[March], 0) + ISNULL([Sales By SKU Units].[April], 0) + 
                ISNULL([Sales By SKU Units].[May], 0) + ISNULL([Sales By SKU Units].[June], 0) + 
                ISNULL([Sales By SKU Units].[July], 0) + ISNULL([Sales By SKU Units].[August], 0) + 
                ISNULL([Sales By SKU Units].[September], 0) + ISNULL([Sales By SKU Units].[October], 0) + 
                ISNULL([Sales By SKU Units].[November], 0) + ISNULL([Sales By SKU Units].[December], 0) 
           ELSE '0' 
    END AS [SALES LY], 
    CASE 
       WHEN [Sales By SKU Units].[Year] = '2017' 
          THEN ISNULL([Sales By SKU Units].[Jan], 0) + ISNULL([Sales By SKU Units].[Feb], 0) + 
               ISNULL([Sales By SKU Units].[March], 0) + ISNULL([Sales By SKU Units].[April], 0) + 
               ISNULL([Sales By SKU Units].[May], 0) + ISNULL([Sales By SKU Units].[June], 0) + 
               ISNULL([Sales By SKU Units].[July], 0) + ISNULL([Sales By SKU Units].[August], 0) + 
               ISNULL([Sales By SKU Units].[September], 0) + ISNULL([Sales By SKU Units].[October], 0) + 
               ISNULL([Sales By SKU Units].[November], 0) + ISNULL([Sales By SKU Units].[December], 0) 
          ELSE '0' 
    END AS [SALES TY], 
    dbo.[Sales By SKU Units].Year
FROM
    dbo.[Sales By SKU Units] 
INNER JOIN
    dbo.[Sales By SKU Value] ON dbo.[Sales By SKU Units].Year = dbo.[Sales By SKU Value].Year 
                             AND dbo.[Sales By SKU Units].SKU = dbo.[Sales By SKU Value].SKU 
                             AND dbo.[Sales By SKU Units].[Customer Account Number] = dbo.[Sales By SKU Value].[Customer Account Number] 
INNER JOIN
    dbo.RangeLists ON dbo.[Sales By SKU Value].SKU = dbo.RangeLists.CustProdRef

I know I need either the GROUP BY or the OVER(partition by) but I just cannot work out how to apply them to my current query.

tsql sql-server
1 Answers

I'm assuming you're looking for the sum of the SALES fields, in which case you need SUM() for your grouping, like this:

select 
    ACC
    , sum(SALES_LY) as SALES_LY
    , sum(SALES_TY) as SALES_TY
    , null as [YEAR]
from 
    ([insert your current query here])
    group by ACC

If you wanted the maximum you'd just use MAX() instead of SUM()

user_65752130
Related