CODEWITHSUNDEEP
sql-servert-sql
Area51Resident
Area51Resident

Reputation: 91

Need to combine (add together) values in a table based on data in a column

I'm trying to solve an issue and have ended up on the rocks...

Data looks like this:

ID, Product_Code, Amount
123, Sandwich_Ham, 2.50
234, Sandwich_Egg, 2.75
123, Sandwich_Mustard, 0.50
123, Coffee, 1.50
456, Sandwich_Beef, 3.75
234, Water, 1.25
456, Sandwich_Horseradish, 0.75
456, Beer, 4.75

Data to be returned to a Crystal Report.

ID, Product_Code, Amount
123, Sandwich_Ham, 3.00
123, Coffee, 1.50
234, Sandwich_Egg, 2.75
234, Water, 1.25
456, Sandwich_Beef, 4.50
456, Beer, 4.75

As you can see in the example above, I need to add together (sum) the amount column for products that have match on the beginning of the Product_Code field and return just one row with the total amount in the Amount column (instead of the two) All other rows pass-through as-is.

For a given ID, there are only two rows that would have matching prefix (in effect only one condiment allowed per sandwich), max one sandwich per person.

The Crystal report filters by ID and has to display every line for a given ID, so no way I can just sum the values in CR. (I've tried.)

I created a scalar-valued function that returns the total amount when passed an ID, but can't figure out how to get that to would for an entire table. Based on my reading, a table-valued function won't help.

My thought is I could write a stored procedure to loop through the table, call the scalar function to handle the case where a product code begins with 'Sandwich_' and write everything out to a temp table. I would use a view to call the stored procedure and return the rows from the temp table. Then modify the report in CR to use the view. Performance could be a big issue with this plus it seems to rather complex for dealing with a 'small change' to an existing report.

Any suggestions on other methods for solving this (before I go another rat hole...)?

Upvotes: 1

Views: 64

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Use LEFT to get the grouping and then do a simple SUM and GROUP BY:

WITH Cte AS(
    SELECT *,
        grp = CASE 
                WHEN CHARINDEX('_', Product_Code) = 0 THEN Product_Code
                ELSE LEFT(Product_Code, CHARINDEX('_', Product_Code) - 1)
              END
    FROM #Tbl
)
SELECT
    ID,
    Product_Code = MAX(Product_Code),
    Amount = SUM(Amount)
FROM Cte
GROUP BY
    ID, grp
ORDER  BY Id;

ONLINE DEMO

Upvotes: 2

Charles Bretana
Charles Bretana

Reputation: 146541

try this as Select in CR

Select id, case when charIndex('_', product_Code) > 0 
                then left(product_Code, charIndex('_', product_Code)
                else product_Code end ProductCategory, 
       sum (Amount) totalAmt
From myTable
Group By id, case when charIndex('_', product_Code) > 0 
                then left(product_Code, charIndex('_', product_Code)
                else product_Code end

Upvotes: 0

Related Questions