akhan
.jpg)
Posts: 38
|
| Posted: 06/14/2006, 4:23 AM |
|
Hi,
Im trying to calculate the sum for all returned rows for a specific column. I have used the following code:
Session ("estBrokerageSUM") += dataItem.estbrokerage.value
This only work for all rows that are currently displayed on the grid, but I want to add up total for all returned results, just like total records found.
Please can some one help?
Thanks in advance.
Abs
_________________
"Simplicity is the key to brilliance” (bruce lee) |
 |
 |
mrachow
Posts: 509
|
| Posted: 06/14/2006, 5:07 AM |
|
I would suggest to extend your SQL query by an additional column with SUM(yourDbColumn).
Using the Visual Query Builder you can enter this under Select | Column/Expression.
_________________
Best regards,
Michael |
|
|
|
akhan
Posts: 38
|
| Posted: 06/14/2006, 5:31 AM |
|
HI,
Thanks for your response, but i cant seem to get this working:
Here is my SQL in VQB:
SELECT *
FROM VW_riskStatus
WHERE division_divisionID = {selectedDivision}
AND ( actionStatusID = 2 OR actionStatusID = 3 )
AND assuredName LIKE '%{s_assuredName}%'
AND riskNo LIKE '%{s_riskNo}%'
ORDER BY targetDate ASC
How can I extend this to include the SUM?
Thanks again,,
Abs
_________________
"Simplicity is the key to brilliance” (bruce lee) |
|
|
|
akhan
Posts: 38
|
| Posted: 06/14/2006, 6:07 AM |
|
BTW,
Im not trying to include a column with the sum in the grid. I want the total to be displayed outside the grid.
_________________
"Simplicity is the key to brilliance” (bruce lee) |
|
|
|
dikmus
Posts: 19
|
| Posted: 06/14/2006, 6:34 AM |
|
You could make a new view that gives only the sum as a resultset
Then outside your grid, you can make a "recordform" that shows the result of that view (the sum)
_________________
www.musit.nl |
|
|
|
mrachow
Posts: 509
|
| Posted: 06/14/2006, 6:36 AM |
|
With Visual Query Builder open you should have a table Select below the table diagram.
There should be one line with '*' in it. Just in the next line you can enter SUM(<yourColumn>).
If you don't tie this extra column to a label in a grid column it wan't be shown there.
To show that sum value. It would be easier when you show this SUM value somewhere inside the form containing the grid (not as column of course) because its easier to access this expression from datasource.
If you need it outside this form we maybe have to talk again.
_________________
Best regards,
Michael |
|
|
|
akhan
Posts: 38
|
| Posted: 06/14/2006, 6:37 AM |
|
I can do it by creating a seperate record form, but its messy.
CCS has already built in pagesCount method, it displays the total records.
Now i have modified the sql to SUM instead of COUNT and its work perfect.
I modified the pagesCount as :
Count=new SqlCommand("SELECT SUM (estBrokerageGBP) AS TotalEstBRokerage FROM (SELECT * " & vbCrLf & _
"FROM VW_riskStatus" & vbCrLf & _
"WHERE division_divisionID = {selectedDivision}" & vbCrLf & _
"AND ( actionStatusID = 2 OR actionStatusID = 3 )" & vbCrLf & _
"AND assuredName LIKE '%{s_assuredName}%'" & vbCrLf & _
"AND riskNo LIKE '%{s_riskNo}%') cnt",Settings.BTSDataAccessObject)
_________________
"Simplicity is the key to brilliance” (bruce lee) |
|
|
|
akhan
Posts: 38
|
| Posted: 06/14/2006, 6:42 AM |
|
Hi Mrachow,
The label is placed out of the begin row. Same row as the total record.
_________________
"Simplicity is the key to brilliance” (bruce lee) |
|
|
|
