Solved

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

Posted on 2008-10-03
11
1,153 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

910 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

17 Experts available now in Live!

Get 1:1 Help Now