Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-10-03
11
Medium Priority
?
1,211 Views
Last Modified: 2013-11-08
Here is my SQL tables.
--------------------
| tblCustomers   |
--------------------
CustomerName
CustomerID
CKDATE
CKNUM
----------------------
| CustomerMoney |
----------------------
CustomerID
DATE
NUMBER
MONEY1
MONEY2
MONEY3
MONEY4
MONEY5
MONEY6
MONEY7

I need to display the following in a data grid view.

CustomerName           CustomerID           Date           Number           Money1           Money2,3,4...    Money7

- Ordered by Date between calendar1.date calendar2.date (Vb.net controls).

The tricky part is this:
If the date changes I want to insert a "total row" which sums up Money1 to Money7.

For Example:
CustomerName           CustomerID          Date           Number           Money1           Money2,3,4...    Money7
'John Smith'                    1                     1/1/2008        1234                $10                 ...etc....              $15
'Joe Dawg'                     2                     1/1/2008        1235                $5                   ...etc....              $10
-Total Row Inserted Here -                                                                 $15                 ...etc....              $25

'Jennifer Smith'              3                     1/2/2008        1236                $20                 ...etc....               $5
'Jack Dawg'                    4                     1/2/2008        1237                $30                   ...etc....             $20
-Total Row Inserted Here -                                                                  $50                 ...etc....              $25

'Ashley Smith'                     5                     1/3/2008        1238           $20                 ...etc....               $5
'Angela Dawg'                    6                     1/3/2008        1239           $30                   ...etc....             $20
'Samatha Young'                7                     1/3/2008        1230           $30                   ...etc....             $20
-Total Row Inserted Here -                                                                $80                 ...etc....              $45


Is this possible? If so, how?
0
Comment
Question by:JohnnyBCJ
  • 5
  • 5
11 Comments
 
LVL 7

Expert Comment

by:the_bachelor
ID: 22635716
if you're trying to do totals for erverything on the grid, you could look into the grid Footer.
0
 

Author Comment

by:JohnnyBCJ
ID: 22635978
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.
0
 
LVL 39

Expert Comment

by:appari
ID: 22658948
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
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:JohnnyBCJ
ID: 22660339
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.
0
 
LVL 39

Expert Comment

by:appari
ID: 22669072
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
0
 
LVL 39

Expert Comment

by:appari
ID: 22669095
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"
0
 

Author Comment

by:JohnnyBCJ
ID: 22669788
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.

0
 
LVL 39

Expert Comment

by:appari
ID: 22674486
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
0
 

Author Comment

by:JohnnyBCJ
ID: 22677028
       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.
0
 
LVL 39

Accepted Solution

by:
appari earned 2000 total points
ID: 22678752
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(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 Disbursements.CKDATE,Disbursements.CKNUM Order by CKDATE,Row_Type
0
 

Author Closing Comment

by:JohnnyBCJ
ID: 31502822
Great Job!
Thanks again
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Loops Section Overview

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question