Solved

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

Posted on 2008-10-03
11
1,145 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
 

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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 500 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now