How do you insert a "total" or "sum" row in a datagridview? (VB.NET)

if you're trying to do totals for erverything on the grid, you could look into the grid Footer.

Unfortunately I'm not. That is causing the extremely tricky part. I need to insert a total row for each individual day so that at a glance you can tell how much money was involved on a particular day.

I need go be able to go through each row and grab the total of Money 1 to 7 (keep each column - Money1 through to 7 - separate) until the Date changes or no more records.

If the date changes (or no more records), I want to insert a row between where the date changes that contains the total of each of the separate money columns (1 through 7).

Unfortunately it's easier to create the theory then it is to create the code.

try this sql

Select 1 Row_Type,
A.CustomerName,
A.CustomerID,
B.Date ,
B.Number, B.Money1, B.Money2
, B.Money3, B.Money4, B.Money5, B.Money6, B.Money7
From
tblCustomers A Join CustomerMoney B
on A.CustomerID= B.CustomerID

union All

Select 2,
'Total',
Null,
B.Date,
Sum(B.Number), Sum(B.Money1), Sum(B.Money2)
, Sum(B.Money3), Sum(B.Money4), Sum(B.Money5)
, Sum(B.Money6), Sum(B.Money7)
From
tblCustomers A Join CustomerMoney B
on A.CustomerID= B.CustomerID
group by B.Date
Order by Date, Row_Type

I tried my best to get what you're trying to get me to do but I'm having zero success.
Here is my code that displays 1 record in the above table - for example the bottom row:
 'Ashley Smith'                     5                     1/3/2008        1238           $20                 ...etc....               $5 (which is 100% correct)

SELECT Clients.LNAME, Clients.FNAME, Clients.SIN,
Disbursements.CKDATE, Disbursements.CKNUM,
SUM(Disbursements.C_SCH1_REFUND) AS [SCH1 REFUND],
SUM(Disbursements.C_PAY_OUT) AS [PAY OUT],
SUM(Disbursements.CKAMT) AS CKAMT,
Disbursements.C_SCH1_REFUND - (Disbursements.C_85PERCENT + Disbursements.C_95PERCENT) AS FEE,
SUM(Receipts.AMT_REC) AS [AMT REC],
SUM(Receipts.INT_AMT) AS [TOTAL INT AMT]
FROM
Clients
INNER JOIN Disbursements ON Clients.SIN = Disbursements.SIN
INNER JOIN Receipts ON Clients.SIN = Receipts.SIN
GROUP BY Clients.LNAME, Clients.FNAME, Clients.SIN,
Disbursements.CKDATE, Disbursements.CKNUM,
Disbursements.C_85PERCENT, Disbursements.C_95PERCENT, Disbursements.C_SCH1_REFUND


I tried my best to change the above into your syntax:

"Select 1 Row_Type,Clients.LNAME, Clients.FNAME,Clients.SIN,
Disbursements.CKDATE,Disbursements.CKNUM,
SUM(Disbursements.C_SCH1_REFUND) AS [SCH1 REFUND],
SUM(Disbursements.C_PAY_OUT) AS [PAY OUT],
SUM(Disbursements.CKAMT) AS CKAMT,
Disbursements.C_SCH1_REFUND - (Disbursements.C_85PERCENT + Disbursements.C_95PERCENT) AS FEE,
SUM(Receipts.AMT_REC) AS [AMT REC],
SUM(Receipts.INT_AMT) AS [TOTAL INT AMT]
from Clients JOIN Disbursements ON Clients.SIN = Disbursements.SIN
union
All Select 2,'Total',Null,Disbursements.CKDATE,Disbursements.CKNUM, Sum(SUM(Disbursements.C_SCH1_REFUND)) AS [SCH1 REFUND], Sum(SUM(Disbursements.C_PAY_OUT)) AS [PAY OUT],
Sum(SUM(Disbursements.CKAMT)) AS CKAMT,
Sum(Disbursements.C_SCH1_REFUND - (Disbursements.C_85PERCENT + Disbursements.C_95PERCENT)) AS FEE,
Sum(SUM(Receipts.AMT_REC)) AS [AMT REC],
Sum(SUM(Receipts.INT_AMT)) AS [TOTAL INT AMT]
from Clients JOIN Disbursements ON Clients.SIN = Disbursements.SIN
group by Disbursements.CKDATE Order by Disbursements.CKDATE,Row_Type"

The error I'm getting is the following:
Column 'Clients.LNAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. The multi-part identifier "Receipts.AMT_REC" could not be bound. The multi-part identifier "Receipts.INT_AMT" could not be bound. The multi-part identifier "Receipts.AMT_REC" could not be bound. The multi-part identifier "Receipts.INT_AMT" could not be bound. All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Any idea? I greatly appreciate the help.

try this

Select
1 Row_Type,
Clients.LNAME,
Clients.FNAME,
Clients.SIN,
Disbursements.CKDATE,
Disbursements.CKNUM,
SUM(Disbursements.C_SCH1_REFUND) AS [SCH1 REFUND],
SUM(Disbursements.C_PAY_OUT) AS [PAY OUT],
SUM(Disbursements.CKAMT) AS CKAMT,
Sum(Disbursements.C_SCH1_REFUND - (Disbursements.C_85PERCENT + Disbursements.C_95PERCENT)) AS FEE,
SUM(Receipts.AMT_REC) AS [AMT REC],
SUM(Receipts.INT_AMT) AS [TOTAL INT AMT]
from Clients JOIN Disbursements ON Clients.SIN = Disbursements.SIN
GROUP BY Clients.LNAME, Clients.FNAME, Clients.SIN,
Disbursements.CKDATE, Disbursements.CKNUM

union All

Select 2,'Total',Null,Disbursements.CKDATE,Disbursements.CKNUM,
Sum(SUM(Disbursements.C_SCH1_REFUND)) AS [SCH1 REFUND],
Sum(SUM(Disbursements.C_PAY_OUT)) AS [PAY OUT],
Sum(SUM(Disbursements.CKAMT)) AS CKAMT,
Sum(Disbursements.C_SCH1_REFUND - (Disbursements.C_85PERCENT + Disbursements.C_95PERCENT)) AS FEE,
Sum(SUM(Receipts.AMT_REC)) AS [AMT REC],
Sum(SUM(Receipts.INT_AMT)) AS [TOTAL INT AMT]
from Clients JOIN Disbursements ON Clients.SIN = Disbursements.SIN
group by Disbursements.CKDATE,Disbursements.CKNUM Order by CKDATE,Row_Type

sorry try this, in your second sql why did you remove join to Receipts table? if you dont need data from Receipts table remove Receipts fields from from list.


Select
1 Row_Type,
Clients.LNAME,
Clients.FNAME,
Clients.SIN,
Disbursements.CKDATE,
Disbursements.CKNUM,
SUM(Disbursements.C_SCH1_REFUND) AS [SCH1 REFUND],
SUM(Disbursements.C_PAY_OUT) AS [PAY OUT],
SUM(Disbursements.CKAMT) AS CKAMT,
Sum(Disbursements.C_SCH1_REFUND - (Disbursements.C_85PERCENT + Disbursements.C_95PERCENT)) AS FEE,
SUM(Receipts.AMT_REC) AS [AMT REC],
SUM(Receipts.INT_AMT) AS [TOTAL INT AMT]
from Clients JOIN Disbursements ON Clients.SIN = Disbursements.SIN
INNER JOIN Receipts ON Clients.SIN = Receipts.SIN
GROUP BY Clients.LNAME, Clients.FNAME, Clients.SIN,
Disbursements.CKDATE, Disbursements.CKNUM

union
All
Select 2,'Total',Null,Disbursements.CKDATE,Disbursements.CKNUM,
Sum(SUM(Disbursements.C_SCH1_REFUND)) AS [SCH1 REFUND],
Sum(SUM(Disbursements.C_PAY_OUT)) AS [PAY OUT],
Sum(SUM(Disbursements.CKAMT)) AS CKAMT,
Sum(Disbursements.C_SCH1_REFUND - (Disbursements.C_85PERCENT + Disbursements.C_95PERCENT)) AS FEE,
Sum(SUM(Receipts.AMT_REC)) AS [AMT REC],
Sum(SUM(Receipts.INT_AMT)) AS [TOTAL INT AMT]
from Clients JOIN Disbursements ON Clients.SIN = Disbursements.SIN
INNER JOIN Receipts ON Clients.SIN = Receipts.SIN
group by Disbursements.CKDATE,Disbursements.CKNUM Order by CKDATE,Row_Type"

I guess I had tunnel vision and wanted to match your syntax exactly.

I get the following error with your SQL statement above:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery. All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

I guess that is because the 1st statement selects 12 different things while the 2nd one selects 11? I might be wrong. I don't know if 'null' counts or not.

try

Select
1 Row_Type,
Clients.LNAME,
Clients.FNAME,
Clients.SIN,
Disbursements.CKDATE,
Disbursements.CKNUM,
SUM(Disbursements.C_SCH1_REFUND) AS [SCH1 REFUND],
SUM(Disbursements.C_PAY_OUT) AS [PAY OUT],
SUM(Disbursements.CKAMT) AS CKAMT,
Sum(Disbursements.C_SCH1_REFUND - (Disbursements.C_85PERCENT + Disbursements.C_95PERCENT)) AS FEE,
SUM(Receipts.AMT_REC) AS [AMT REC],
SUM(Receipts.INT_AMT) AS [TOTAL INT AMT]
from Clients JOIN Disbursements ON Clients.SIN = Disbursements.SIN
INNER JOIN Receipts ON Clients.SIN = Receipts.SIN
GROUP BY Clients.LNAME, Clients.FNAME, Clients.SIN,
Disbursements.CKDATE, Disbursements.CKNUM

union
All
Select 2,
'Total',
Null,
Null,
Disbursements.CKDATE,
Disbursements.CKNUM,
Sum(SUM(Disbursements.C_SCH1_REFUND)) AS [SCH1 REFUND],
Sum(SUM(Disbursements.C_PAY_OUT)) AS [PAY OUT],
Sum(SUM(Disbursements.CKAMT)) AS CKAMT,
Sum(Disbursements.C_SCH1_REFUND - (Disbursements.C_85PERCENT + Disbursements.C_95PERCENT)) AS FEE,
Sum(SUM(Receipts.AMT_REC)) AS [AMT REC],
Sum(SUM(Receipts.INT_AMT)) AS [TOTAL INT AMT]
from Clients JOIN Disbursements ON Clients.SIN = Disbursements.SIN
INNER JOIN Receipts ON Clients.SIN = Receipts.SIN
group by Disbursements.CKDATE,Disbursements.CKNUM Order by CKDATE,Row_Type

       Dim da As New SqlDataAdapter("Select 1 Row_Type,Clients.LNAME,Clients.FNAME,Clients.SIN,Disbursements.CKDATE,Disbursements.CKNUM,SUM(Disbursements.C_SCH1_REFUND) AS [SCH1 REFUND],SUM(Disbursements.C_PAY_OUT) AS [PAY OUT],SUM(Disbursements.CKAMT) AS CKAMT,Sum(Disbursements.C_SCH1_REFUND - (Disbursements.C_85PERCENT + Disbursements.C_95PERCENT)) AS FEE,SUM(Receipts.AMT_REC) AS [AMT REC],SUM(Receipts.INT_AMT) AS [TOTAL INT AMT] from Clients JOIN Disbursements ON Clients.SIN = Disbursements.SIN INNER JOIN Receipts ON Clients.SIN = Receipts.SIN GROUP BY Clients.LNAME, Clients.FNAME, Clients.SIN, Disbursements.CKDATE, Disbursements.CKNUM union All Select 2, 'Total',Null,Null,Disbursements.CKDATE,Disbursements.CKNUM,Sum(SUM(Disbursements.C_SCH1_REFUND)) AS [SCH1 REFUND],Sum(SUM(Disbursements.C_PAY_OUT)) AS [PAY OUT],Sum(SUM(Disbursements.CKAMT)) AS CKAMT,Sum(Disbursements.C_SCH1_REFUND - (Disbursements.C_85PERCENT + Disbursements.C_95PERCENT)) AS FEE,Sum(SUM(Receipts.AMT_REC)) AS [AMT REC],Sum(SUM(Receipts.INT_AMT)) AS [TOTAL INT AMT] from Clients JOIN Disbursements ON Clients.SIN = Disbursements.SIN INNER JOIN Receipts ON Clients.SIN = Receipts.SIN group by Disbursements.CKDATE,Disbursements.CKNUM Order by CKDATE,Row_Type", myConnection)

        da.Fill(ds)
        dgvFooter.DataSource = ds.Tables(0)
        dgvFooter.AutoResizeColumns()
        myConnection.Close()

I get the error: Cannot perform an aggregate function on an expression containing an aggregate or a subquery. The error is on da.Fill(ds). I wish I could give you the points just to show my appreciation of your effort.

Great Job!
Thanks again