SUM AND GROUP PRODUCTS BY SIMILAR CODES/CATEGORY-CodePudding

My problem is that in the supplier's invoices I have the same product with different names. The goal I'd like to reach is to group them in a single product code/category. Do you think is possible?

I want to sum specific record in my SQL Server table. I have something like this

Description	Quantity
Phone X	10
AB Laptop B	20
X Phone	15
Laptop C	20
AB Phone X	5

And I have to sum "Phone" and "Laptop", to obtain a table like this

Description	Quantity
Phone	30
Laptop	40

I have no idea which query is correct to use. The problem is the words "Laptop" and "Phone" contains other words, and I can't sum them.

Can someone help me, please?

CodePudding user response：

Word of Warning:

As others mentioned, parsing string values this will probably lead to data issues. Maybe not today, but down the road. For example, what if someone listed "Laptop w/ built in wireless phone charger". Depending on how this query is used, could cause significant issues.

Recommended Approach to Categorizing Data:

I've worked with many external data feeds and if it were me, I'd recommend doing one of the following:

Define a list of your own product categories and have your vendors map to them in their data feeds
Ask your vendors to add their own internal product category to the data feed, and then map their categories to your own

There are pros and cons to both approaches, but both would consistently provide accurate data.

Group By Category from Key Word in Description

With that fair word of caution, here's how I'd do it if you can't have your vendors change their data feeds:

SELECT Category
    ,TotalQuantity = SUM(Quantity)
FROM YourTable AS A
CROSS APPLY (
    SELECT Category = CASE
                        WHEN [Description] LIKE '%Phone%' THEN 'Phone'
                        WHEN [Description] LIKE '%Laptop%' THEN 'Laptop'
                        ELSE 'Other'
                    END
) AS B
GROUP BY Category

CodePudding user response：

Of course this could be done in SQL alone, but since you have tagged with vb.net, maybe you want a .NET solution. Also, since you supplied no code, I will make some up

Public Class Data
    Public Property Description As String
    Public Property Quantity As Integer
End Class

Dim datas = New List(Of Data)()
datas.Add(New Data With {.Description = "Phone X", .Quantity = 10})
datas.Add(New Data With {.Description = "AB Laptop B", .Quantity = 20})
datas.Add(New Data With {.Description = "X Phone", .Quantity = 15})
datas.Add(New Data With {.Description = "Laptop C", .Quantity = 20})
datas.Add(New Data With {.Description = "AB Phone X", .Quantity = 5})

Dim groupedRecords = datas.
    GroupBy(Function(r) If(r.Description.ToUpper().Contains("PHONE"), "Phone", "Laptop")).
    Select(Function(g) New With {.Description = g.Key, .Quantity = g.Sum(Function(i) i.Quantity)})

For Each record In groupedRecords
    Debug.Print($"Description: {record.Description}, Quantity: {record.Quantity}")
Next

Description: Phone, Quantity: 30
Description: Laptop, Quantity: 40

If you add more products, this will break. It recognizes anything with "phone" in the description in it as a Phone, and anything else as a laptop.

CodePudding user response：

Stephan's answer was my first thought 1, however, I tend prefer to keep such items in generic mapping table and not code ... too many touchpoints

Imagine @Map was a physical table

Example

 Declare @Map Table (Item varchar(50))
 Insert Into @Map values
 ('Phone')
,('Laptop')
,('Cable')
,('Battery')


Select ItemGrp = coalesce(B.Item,'Undefined')
      ,Quantity = sum([Quantity])
from   YourTable A
Left Join  @Map B on [Description] like '%' Item '%'
Group By B.Item

Results

ItemGrp Quantity
Laptop  40
Phone   30