asked on

How to replicate adding 2 sumif functions in SQL

Background: I am very new to SQL but here it goes: A serial contains 2 bits of info. It tells what the "parent" with the first 6 characters, and then tells who the children are with the characters after that. So e.g. B12345 is the parent, and B12345999, B12345631, B12345110 are the children. The DB has a column, called Serial_Number and it lists all of this, and there are costs associated with producing this product, which goes through multiple stages (casting, molding, finishing, etc..)

What I would like to do is sum all of the costs that went into making that child and add that as a value in the record as part of a new column called total_child_cost. Where the values in the column would be all of the costs associated with producing that child. So in Excel, it works perfect with 2 SUMIF statements but I can't replicate it in SQL. The value would show up WHERE the pdn_process = Packing since 'Packing' is the last stage in the process. So it would sum all of the costs went into making the child.

This was my attempt:

UPDATE data
    SET total__child_cost = 
        (CASE WHEN length(serial) > 6
                    AND process = 'Packing'
                        THEN
                            IF(serial = serial, sum(process_cost),0) END)
                            +
                            (CASE WHEN left(serial,6) = serial
                                THEN sum(process_cost)
                            END)

Open in new window

You can replicate the situation with this CREATE code:

CREATE TABLE data (
  serial text,
  process text,
  process_cost double,
  total_child_cost double,
  process_id text
  );
   
 INSERT INTO data (serial, process, process_cost, process_id)
   VALUES ('BSA101A33', 'Packing', 10, 6);
INSERT INTO data (serial, process, process_cost, process_id)
   VALUES ('BSA101A34', 'Packing', 10, 6);
INSERT INTO data (serial, process, process_cost, process_id)
   VALUES ('BSA101', 'Cast', 50, 1);
INSERT INTO data (serial, process, process_cost, process_id)
    VALUES ('BSA101', 'Mold', 30, 2);
INSERT INTO data (serial, process, process_cost, process_id)
   VALUES ('BSA101', 'Mold', 30, 3);
INSERT INTO data (serial, process, process_cost, process_id)  
   VALUES ('BSA101A33', 'Finish', 15, 5);
INSERT INTO data (serial, process, process_cost, process_id)
   VALUES ('BSA101A34', 'Finish', 25, 5);
INSERT INTO data (serial, process, process_cost, process_id)
   VALUES ('BSA101A33', 'Polish', 25, 4);
INSERT INTO data (serial, process, process_cost, process_id)
   VALUES ('BSA101A34', 'Polish', 50, 4);

Open in new window

Mike Eghtebas

Please post sample output for this. Also, thanks for the TSQL you have put together.

Mike

Aspiring Developer

ASKER

This would be the optimal output:

Mike Eghtebas

could you please post your sumif function from excel?

Aspiring Developer

ASKER

Sure thing

=IF(LEN([serial])>6,IF([process]="Packing",SUMIF([serial],[@serial],[process_cost])+SUMIF([serial],LEFT([@serial],6),[process_cost]),"NULL"),"NULL")

ignore the BSA102 as 'Packing', did not edit that other example correctly.

Mike Eghtebas

To simplify the problem, you have provided data only one parent serial number (BSA101). Could you please post data for one or two more serial number along with their child processes. This way it could be tested to make sure it will work for any number of serial numbers. You don't have to make TSQL if you don't have it but post data in electronic form not as image.

Thank you

Aspiring Developer

ASKER

I attached an .xlsx table
Example.xlsx

Aspiring Developer

ASKER

And thank you so much for helping me out with this!

Nitin Sontakke

Hello,

I struggled a lot with joins and CTE to achieve the desired result but, unfortunately, no success. Finally, i resigned and resorted to the UDF. Not necessarily a bad thing, though!

I suggest you wait for answers from others.

Here is entire script

create function getProcessCost
(
  @serial varchar(20)
)
returns decimal(10, 2)
as
begin
  declare @rv1 decimal(10, 2) = 0.00
  declare @rv2 decimal(10, 2) = 0.00

  select @rv1 = sum([process_cost])
  from [data]
  where [serial] = @serial
    and [process_id] != 6

  select @rv2 = sum([process_cost])
  from [data]
  where substring([serial], 1, 6) = substring(@serial, 1, 6)
    and len([serial]) = 6

  return @rv1 + @rv2;
end
go

CREATE TABLE [data] (
  serial varchar(20),
  process varchar(20),
  process_cost decimal(10, 2),
  total_child_cost decimal(20, 2),
  process_id int
  );
   
insert into [data] (serial, process, process_cost, process_id) values
('BSA101A33', 'Packing', 10.00, 6)
,('BSA101A34', 'Packing', 10.00, 6)
,('BSA101', 'Cast', 50.00, 1)
,('BSA101', 'Mold', 30.00, 2)
,('BSA101', 'Mold', 30.00, 3)
,('BSA101A33', 'Finish', 15.00, 5)
,('BSA101A34', 'Finish', 25.00, 5)
,('BSA101A33', 'Polish', 25.00, 4)
,('BSA101A34', 'Polish', 50.00, 4)
,('BSA102A33', 'Packing', 30.00, 6)
,('BSA102A34', 'Packing', 40.00, 6)
,('BSA102', 'Cast', 50.00, 1)
,('BSA102', 'Mold', 30.00, 2)
,('BSA102', 'Mold', 30.00, 3)
,('BSA102A33', 'Finish', 15.00, 5)
,('BSA102A34', 'Finish', 25.00, 5)
,('BSA102A33', 'Polish', 25.00, 4)
,('BSA102A34', 'Polish', 50.00, 4)
,('BSA103A33', 'Packing', 10.00, 6)
,('BSA103A34', 'Packing', 10.00, 6)
,('BSA103', 'Cast', 40.00, 1)
,('BSA103', 'Mold', 20.00, 2)
,('BSA103', 'Mold', 30.00, 3)
,('BSA103A33', 'Finish', 15.00, 5)
,('BSA103A34', 'Finish', 25.00, 5)
,('BSA103A33', 'Polish', 25.00, 4)
,('BSA103A34', 'Polish', 50.00, 4)

select *, dbo.[getProcessCost](d.[serial]) + d.[process_cost]
from [data] d
where d.[process_id] = 6

Open in new window

Mike Eghtebas

This is progress version. Need to work on it a little bit more:

@data is a temp table variable. From line 20 onward, drop @ making it read data from your own table also named data:

USE [EE]
declare @data Table ( 
  serial varchar(20),
  process varchar(20),
  process_cost decimal(4, 2),
  process_id int
  );

INSERT INTO @data (serial, process, process_cost, process_id) VALUES
('BSA101A33', 'Packing', 10, 6), ('BSA101A34', 'Packing', 10, 6), ('BSA101', 'Cast', 50, 1)
, ('BSA101', 'Mold', 30, 2), ('BSA101', 'Mold', 30,	3), ('BSA101A33', 'Finish',	15,	5)
, ('BSA101A34', 'Finish', 25, 5), ('BSA101A33', 'Polish', 25, 4), ('BSA101A34', 'Polish', 50, 4)
, ('BSA102A33', 'Packing', 30, 6), ('BSA102A34', 'Packing',	40,	6), ('BSA102', 'Cast', 50,	1)
, ('BSA102', 'Mold', 30,	2), ('BSA102', 'Mold',	30,	3), ('BSA102A33', 'Finish',	15,	5)
, ('BSA102A34', 'Finish', 25, 5), ('BSA102A33', 'Polish', 25, 4), ('BSA102A34', 'Polish', 50,	4)
, ('BSA103A33', 'Packing', 10, 6), ('BSA103A34', 'Packing',	10,	6), ('BSA103', 'Cast', 40, 1)
, ('BSA103', 'Mold', 20,	2), ('BSA103', 'Mold',	30,	3), ('BSA103A33', 'Finish',	15,	5)
, ('BSA103A34', 'Finish', 25, 5), ('BSA103A33', 'Polish', 25, 4), ('BSA103A34', 'Polish', 50, 4)

select serial, process, process_cost, (Select sum(process_cost)  from @data) not_done_yet --total_child_cost
from @data


;with cte as
(select serial, left(serial, 6) As S1, right(serial, 3) As S2,
 process, process_cost, process_id from @data)
 Select serial, process,s1, s2, sum(process_cost) as total
 from cte Group By serial, process, s1, s2

Open in new window

Aspiring Developer

ASKER

I don't know if this helps but I can add columns to the DB since it's something stored locally. Would it help if I had 2 helper columns? Say Child Cost & Parent Cost where I can split the serial out into 2 columns? So all in all create 4 new columns.

Column 1 = Parent so left(serial, 6)
Column 2 = Child so CASE WHEN length(serial) > 6, then serial else null ;
Column 3 = Parent Cost, some type of CASE When parent = Serial THEN SUM (Process_Cost) ELSE 0 < - - formula would run @ packing stage
Column 4 = Child Cost, CASE WHEN child = SERIAL THEN SUM(process_cost) ELSE 0 < - - formula would run @ packing stage
Column 5 = parent + Child cost

Mike Eghtebas

I had to run some errands but I am back working at it. Your willingness to add some new helper columns surely could help. I have created these helper columns in a CTE; having them in the table possibly could ease the operation. As I am working on it now, I will be able to respond to your last post.

The drawback will be having redundant info in the database which could cause confusion later on. I will be back on this a bit later.

Thank you,

Mike

Mike Eghtebas

The below image is the output my latest solution produces. Please take a look at it so I could fix any mistakes I may have before I submit it.

The new column Type is product type I included so that the user can distinguish one from another. These numbers are based on first 6 characters of the serial numbers (from left). It starts from 1 and then it gets incremented by one with each new type.

I have done quite bit manufacturing in the past so your question was interesting to me.

Thanks,

Mike

ASKER CERTIFIED SOLUTION

Mike Eghtebas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aspiring Developer

ASKER

Thank you so much Mike!