Have Geek Brain

SQL The aggregate of all purchases for a specific product with the same discount code

I would like to combine all purchases for a specific product that used the same discount code (using SQL Server 2005).

Suppose we have the following table:

ID ProductID Product RebateCode Amount
1 123 7HM ABC 1
2 123 7HM XYZ 2
3 124 7HM ABC 10
4 124 7HM XYZ 20
5 125 7HM ABC 100
6 125 7HM XYZ 200
7 125 7HM CEX 3
8 126 2EA ECU 4
8 127 2EA ECU 40
9 128 2EB FBA 5
9 129 2EB FBA 50
10 130 2EB FBA 500

It can be created with the following SQL.

CREATE TABLE #ProductSales (ID SMALLINT, ProductID int, Product varchar (6), RebateCode varchar (4), Amount int)
GO
INSERT INTO #ProductSales
  select 1, 123, '7HM', 'ABC', 1 union all
  select 2, 123, '7HM', 'XYZ', 2 union all
  select 3, 124, '7HM', 'ABC', 10 union all
  select 4, 124, '7HM', 'XYZ', 20 union all
  select 5, 125, '7HM', 'ABC', 100 union all
  select 6, 125, '7HM', 'XYZ', 200 union all
  select 7, 125, '7HM', 'CEX', 3 union all
  select 8, 126, '2EA', 'ECU', 4 union all
  select 8, 127, '2EA', 'ECU', 40 union all
  select 9, 128, '2EB', 'FBA', 5 union all
  select 9, 129, '2EB', 'FBA', 50 union all
  select 10, 130, '2EB', 'FBA', 500
GO

And I would like to get the following result. As you can see, since 7HM was used in 2 different combinations (2 times ABC + XYZ) and 1 time (ABC + CEX + XYZ), it was split into two lines:

Product nrOfProducts rebateCodeCombination SumAmount ABC CEX ECU FBA XYZ
7HM 2 ABC, XYZ 33 11 0 0 0 22
7HM 1 ABC, CEX, XYZ 303 100 3 0 0 200
2EB 3 FBA 555 0 0 0 555 0
2EA 2 ECU 44 0 0 44 0 0
..

Thomas's proposed solution (see below) is already pretty close.

I modified Thomas's solution slightly to reflect the changes in my example and differ from the productID.

My minor changes to Thomas's solution

Select PS.Product
    , Count (Distinct ProductID) As NrOfProducts
    , Stuff (
            (
                Select ',' + PS1.RebateCode
                From #ProductSales As PS1
                Where PS1.Product = PS.Product
                Order By PS1.RebateCode
                For Xml Path ('')
            ), 1, 2, '') As ComboRebateCode
    , Sum (PS.Amount) As Amount
    , Sum (Case When PS.RebateCode = 'ABC' Then PS.Amount End) As [ABC]
    , Sum (Case When PS.RebateCode = 'CEX' Then PS.Amount End) As [CEX]
    , Sum (Case When PS.RebateCode = 'ECU' Then PS.Amount End) As [ECU]
    , Sum (Case When PS.RebateCode = 'FBA' Then PS.Amount End) As [FBA]
    , Sum (Case When PS.RebateCode = 'XYZ' Then PS.Amount End) As [XYZ]
From #ProductSales As PS
Group By PS.Product

Thomas's solution (with my minor modifications) gives this result

Product NrOfPrd ComboRebateCode Amount ABC CEX ECU FBA XYZ
7HM 3 ABC, [...], XYZ 336 111 3 NULL NULL 222
2EB 3 FBA, FBA, FBA 555 NULL NULL NULL 555 NULL
2EA 2 ECU, ECU 44 NULL NULL 44 NULL NULL

This is already great, there are only 2 things:

7HM 3 ABC, ABC, ABC, CEX, XYZ, XYZ, XYZ ...
                                              instead of 2 results
7HM 2 ABC, XYZ ...
7HM 1 ABC, CEX, XYZ,

And currently it concatenates every rebate code:

2EB 3 FBA, FBA, FBA
7HM 2 ABC, ABC, XYZ, XYZ
                                                    instead of
2EB 3 FBA
7HM 2 ABC, XYZ

Do you know how and where to make changes to achieve the result (only identical combinations of skin codes for a specific product are aggregated (2 7HM and 1 7HM), a combination of a discount code without repeating a discount code (so "ABC, XYZ" instead of "ABC, ABC , XYZ, XYZ ") and, if possible, 0 instead of NULL?

Just clean up (drop temporary tables):

    - Drop Table #ProductSales
    IF EXISTS (
     SELECT *
      FROM tempdb.dbo.sysobjects
      WHERE name LIKE '# ProductSales%')
      DROP TABLE #ProductSales
+2


a source to share


3 answers


Assuming the skin codes are corrected in the number, you can do something like:

Select PS.Product
    , Count(PS.ProductId) As NrOfProducts
    , Stuff(
            (
            Select ', ' + PS1.RebateCode
            From #ProductSales As PS1
            Where PS1.Product = PS.Product
            Order By PS1.RebateCode
            For Xml Path('')
            ), 1, 2, '') As CombinationRebateCode
    , Sum(PS.Amount) As Amount
    , Sum( Case When PS.RebateCode = 'ABC' Then PS.Amount End ) As [ABC]
    , Sum( Case When PS.RebateCode = 'LOI' Then PS.Amount End ) As [LOI]
    , Sum( Case When PS.RebateCode = 'XYZ' Then PS.Amount End ) As [XYZ]
From #ProductSales As PS
Group By PS.Product


If the number of discount codes can be variable, then the only way to achieve this is with some dynamic SQL. If you want dynamic columns, then I would build this result set in middle tier or client code or using a reporting tool.

+2


a source


SQL has a summary command that is not as well known. There should be something like:



select *
from #ProductSales
pivot
(
  sum(Amount)
  for RebateCode in ([ABC],[XYZ],[LOI])
)
as p
+1


a source


If the number of discount codes is below a certain maximum, you can write a PIVOT query to turn your rows into columns. If there is no maximum and you don't mind dynamic SQL, then you can generate a query dynamically (a pivot based threshold.) Both of these approaches are detailed in this article - Rotations with Dynamic SQL

+1


a source






More articles:


All Articles