SUMIF SQL VIA OLEDB in VB.NET

Question

I want sumif results in the database "GSGTS/t3" in the "BLC" column with the number criteria in the "PNM" column. I haven't been able to create sql for the sumif process. With the explanation below :

• the result of the value of 185000 for the number in the column "PNM" which is 1000

QTY  CIU        DPR
10    100000     5   =10X10000X(1-5/100)= 95000
5     20000      10  =5X20000X(1-10/100)= 95000
TOTAL                =185000   

GSDTS AS t1                 
PNM     ITM        QTY  CIU    NOD  DPR
1000    TEST 1000   10  10000   01  5
1000    TEST 1001   5   20000   02  10
1002    TEST 1000   12  10000   01  15
1002    TEST 1001   6   20000   02  6

GSGTS AS t3                             
CSB GDN             PNM      DTS     DUD    SAC  DIS    BLC  DIS2
    A.04.01.002.001 1000    13-May-22       01-01           
    A.04.01.002.001 1002    13-May-22       01-02           
result                              
GSGTS AS t3                             
CSB GDN              PNM    DTS       DUD   SAC  DIS    BLC    DIS2
    A.04.01.002.001 1000    13-May-22       01-01       185000  
    A.04.01.002.001 1002    13-May-22       01-02       214800  

Private Sub fillDataGridView3()
        Try
            'Dim query As String = "select PNM, sum((qty*ciu)*(1-dpr/100)) AS BLC from GSDTS group by PNM"
 Dim sql As String = "update GSGTS SET BLC=( select sum((qty*ciu)*(1-dpr/100.0)) from GSDTS t2 WHERE GSGTS.[PNM] = t2.[PNM] ) WHERE GDN = 'A.04.01.002.001';"
            Using con As OleDbConnection = New OleDbConnection(cn)
                Using cmd As OleDbCommand = New OleDbCommand(query, con)
                    'cmd.Parameters.AddWithValue("@CODE", ComboBox1.SelectedValue)
                    Using da As New OleDbDataAdapter(cmd)
                        Dim dt As DataTable = New DataTable()
                        da.Fill(dt)
                        da.Dispose()
                        source3.DataSource = dt
                        Me.DataGridView3.DataSource = source3
                        Me.DataGridView3.Refresh()
                    End Using
                End Using
            End Using
        Catch ex As Exception
        End Try
    End Sub
Sub UpdateGsgts()
    Try
        'Dim sql As String = "update GSGTS as t3 inner join GSDTS as t2 on t3.[PNM] = t2.[PNM] set t3.[BLC] = [CIU] WHERE GDN = 'A.04.01.002.001'AND PNM=@PNM"
           Dim sql As String = "select PNM, sum((qty*ciu)*(1-dpr/100)) AS BLC from GSDTS group by PNM"
        Using conn As New OleDbConnection(cn),
               cmd As New OleDbCommand(sql, conn)
               'cmd.Parameters.AddWithValue("@PNM", ComboBox1.SelectedValue)
            conn.Open()
            cmd.ExecuteNonQuery()
        End Using

    Catch myerror As OleDbException
        MessageBox.Show("Error: " & myerror.Message)
    End Try
End Sub

Answer 1

You can calculate PNM wise BLC as below:

Schema and insert statements:

 create table GSDTS ( PNM int, ITM varchar(100), QTY int, CIU int, NOD int, DPR int);
 insert into GSDTS values(1000,    'TEST 1000',   10,  10000,   01,  5);
 insert into GSDTS values(1000,    'TEST 1001',   5,   20000,   02,  10);
 insert into GSDTS values(1002,    'TEST 1000',   12,  10000,   01,  15);
 insert into GSDTS values(1002,    'TEST 1001',   6,   20000,   02,  6);

Query:

 select PNM, sum((qty*ciu)*(1-dpr/100)) BLC from GSDTS
 group by PNM

Output:

PNM	BLC
1000	185000.0000
1002	214800.0000

db<>fiddle here

Update query:

Schema and insert statements:

create table GSDTS ( PNM int, ITM varchar(100), QTY int, CIU int, NOD int, DPR int);
   insert into GSDTS values(1000,    'TEST 1000',   10,  10000,   01,  5);
   insert into GSDTS values(1000,    'TEST 1001',   5,   20000,   02,  10);
   insert into GSDTS values(1002,    'TEST 1000',   12,  10000,   01,  15);
   insert into GSDTS values(1002,    'TEST 1001',   6,   20000,   02,  6);
   
   
   create table GSGTS (GDN varchar(50), PNM int, BLC int);
   insert into GSGTS values('A.04.01.002.001',1000,    0);
   insert into GSGTS values('A.04.01.002.001',1002,    0);

Select from GSGTS table:

 select * from GSGTS

Output:

| GDN             |  PNM | BLC
| :-------------- | ---: | --:
| A.04.01.002.001 | 1000 |   0
| A.04.01.002.001 | 1002 |   0

Update query:

update GSGTS SET BLC=( select sum((qty*ciu)*(1-dpr/100.0)) from GSDTS t2 WHERE GSGTS.[PNM] = t2.[PNM] ) WHERE GDN = 'A.04.01.002.001';

Select from GSGTS table after update:

select * from GSGTS

Output:

| GDN             |  PNM |    BLC
| :-------------- | ---: | -----:
| A.04.01.002.001 | 1000 | 185000
| A.04.01.002.001 | 1002 | 214800

db<>fiddle here