Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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,200 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

722 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