# Distinct Total

Sep 5, 2007

Hi,

I hope someone can help with my problem described below.

I have a dataset with the following information....

Code1 Amount Code2

------------ ----------- --------------------

56209087 249.35 N01L1

56209087 249.35 N01L2

56209087 249.35 N02L1

56209903 437.52 N04L1

56209903 437.52 N01L1

56209903 437.52 N01L2

56209903 437.52 N03L1

I need my SSRS report to display a total field which is the sum of the distinct "Amount" values.... (249.35 + 437.52) = 686.87. I need to get the distinct amount per Code1 and then sum these distinct amounts.

Thanks

## Running Total: Summing On Distinct Amount

May 9, 2007

I am trying to do a sum on a goal amount that is repeated for each record. But what is the the forumla to only sum on the distinct goal amount.

Example:

Month Year Goal Other Value

March 2007 500 5568

March 2007 500 5568

March 2007 500 5569

April 2007 600 5568

April 2007 700 5569

March-April 2007 1600 5568

March-April 2007 1200 5569

Total (I excpect)

March-April 2007 1100 5568

March-April 2007 1200 5569

I haven't found anything online to help.

Thanks,

Sam

## Distinct Count With Null Values (grand Total)

May 9, 2007

Hello,

I have a DB of professors and information related with them. I created the cube, it consist of:
Measures:
Measure group Professors:
Amount of projects (COUNT proj_id)
Amount of pulications (COUNT pub_id)
Amount of e_books (COUNT book_id)
--------------
Measure group Projects:
Distinct amount of projects (DISTINCT COUNT proj_id)
--------------
Measure group Publications:
Distinct amount of publications (DISTINCT COUNT pub_id)
--------------
Measure group E_books:
Distinct amount of e_books (DISTINCT COUNT book_id)
--------------
Calculated measures:
Amnt_Projects
iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])
Amnt_Publications
(similar to the above one)
Amnt_E_books
(similar to the above one)
---------------------------
Dimensions:
dimPROFESSORS
- prof_id
-surname
-name
-gender
dimPROJECTS
- proj_id
-type name
-name
dimPUBLICATIONS
- pub_id
-type name
-name
dimE_BOOKS
- book_id
-name
Date_Projects
-date_id
-years
Date_Publications
-date_id
-years
Date_E_books
-date_id
-years

For example, when I browse the cube:
prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects
1032------------------- 30 --------------------------1----------------1
1070-------------------90 --------------------------2----------------2
1111-------------------0 ---------------------------1----------------0
1137-------------------0 ---------------------------1----------------0
1234-------------------1404--------------------------9----------------9
1721-------------------504--------------------------7----------------7
2661-------------------85 --------------------------5----------------5
...--------------------...---------------------------...----------------...
6999------------------- 20---------------------------1-----------------1
9956-------------------50---------------------------5-----------------5
Unknown----------------(empty)---------------------(empty)-----------0
Grand Total------------ 2421------------------------11-----------------11

Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be „59“ if for all professors). How could I get the right value to be shown in Grand Total?

## Distinct Count With Null Values (grand Total)

May 10, 2007

Hello,

I am using SQL Server 2005. I have a DB of professors and information related with them. I created the cube, it consist of:
Measures:
Measure group Professors:
Amount of projects (COUNT proj_id)
Amount of publications (COUNT pub_id)
Amount of e_books (COUNT book_id)
--------------
Measure group Projects:
Distinct amount of projects (DISTINCT COUNT proj_id)
--------------
Measure group Publications:
Distinct amount of publications (DISTINCT COUNT pub_id)
--------------
Measure group E_books:
Distinct amount of e_books (DISTINCT COUNT book_id)
Calculated measures:
Amnt_Projects
iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])
Amnt_Publications
(similar to the above one)
Amnt_E_books
(similar to the above one)
---------------------------
Dimensions:
dimPROFESSORS
- prof_id
-surname
-name
-gender
dimPROJECTS
- proj_id
-type name
-name
dimPUBLICATIONS
- pub_id
-type name
-name
dimE_BOOKS
- book_id
-name
Date_Projects
-date_id
-years
Date_Publications
-date_id
-years
Date_E_books
-date_id
-years

For example, when I browse the cube:
prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects
1032------------------- 30 --------------------------1----------------1
1070-------------------90 --------------------------2----------------2
1111-------------------0 ---------------------------1----------------0
1137-------------------0 ---------------------------1----------------0
1234-------------------1404--------------------------9----------------9
1721-------------------504--------------------------7----------------7
2661-------------------85 --------------------------5----------------5
...--------------------...---------------------------...----------------...
6999------------------- 20---------------------------1-----------------1
9956-------------------50---------------------------5-----------------5
Unknown----------------(empty)---------------------(empty)-----------0
Grand Total------------ 2421------------------------11-----------------11

Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be „59“ if for all professors). How could I get the right value to be shown in Grand Total?

## SQL 2012 :: Get Total Number Of Each Distinct Value In Column And Write To Another Table

Jul 14, 2014

How can i get the total number of each distinct value in the column and write it to another table e.g.

MyTable

Id Fruit
1 Apple
2 Banana
3 Apple
4 Watermelon
5 Banana
6 Watermelon
7 Apple

Result

Fruit Count
Apple 3
Banana 2
Watermelon 2

## Err On Grand Total In Cube Measure Using A Distinct Count Agregation

May 28, 2007

Hi,

I'm using sql server analysis services 2000, i have a problem with grand total when i select more than one member of any demension. the measure of this cube is built on a distinct count agregation function. i have only one measure in th cube

can some body help me !!

## Total Page Writes/total Amount Of Data Change In A Set Period Of Time

Jun 9, 2004

Does anyone know how I can determine the number of page writes that have been performed during a set period of time? I need to figure out the data churn in that time period.

TIA

## Aggregated Subquery - Sum Total Trips And Total Values As Separate Columns By Day

Feb 26, 2014

Very new to SQL and trying to get this query to run. I need to sum the total trips and total values as separate columns by day to insert them into another table.....

My code is as follows;

Insert Into [dbo].[CombinedTripTotalsDaily]
(
Year,
Month,
Week,
DayNo,
Day,
Trip_Date,

[Code] .....

## Query By Year Group By Total Students Sort By Total For Each County

Jul 20, 2005

I haven't a clue how to accomplish this.All the data is in one table. The data is stored by registration dateand includes county and number of students brokne out by grade.Any help appreciated!Rob

## Reporting Services :: SSRS Group Total Expression - Add Total Disabled

Oct 26, 2015

For some reason my Add Total is grey out, when i tried to add grand total using some expression.

I have two row & two column groups?

Is there any alternative or how can i enable add total? using expression..as you can see in my Attached Image

I'm using iff condition in my expression..Â

## SQL Server 2008 :: Pulling Daily Total From Cumulative Total

Jun 28, 2015

I have a table that writes daily sales each night but it adds the day's sales to the cumulative total for the month. I need to pull the difference of todays cumulative total less yesterdays. So when my total for today is 30,000 and yesterday's is 28,800, my sales for today would be 1,200. I want to write this to a new field but I just can't seen to get the net sales for the day. Here is some sample data. For daily sales for 6-24 I want to see 2,000, for 6-25 3,000, 6-26 3,500, and 6-27 3,500. I'm thinking a case when but can't seem to get it right.

CREATE TABLE sales
(date_created date,
sales decimal (19,2))
INSERT INTO sales (date_created, sales)
VALUES ('6-23-15', '20000.00'),
('6-24-15', '22000.00'),
('6-25-15', '25000.00'),
('6-26-15', '28500.00'),
('6-27-15', '32000.00')

## Total Record Count - Pagination With Total Rows

Jul 26, 2013

My table contains 1000 records,

I need to know the total record count with the below paging query

SELECT EmpName,Place
FROM EmplyeeDetails ORDER BY Place
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

How to get?

## Adding Subreport Total To Main Report Total

Apr 28, 2006

Hi, can anyone help?

I have created a Report using Visual studio-the report displays a subreport within it.

On the Subjective Report I have 12 values for each month of the year.

For the first month the value is =sum(Fields! Month_1.Value), and I
have named this text box €™SubRepM1€™
The name of the subreport is €˜subreport1'.

On my Main Report, again I have 12 values for each month of the year.
For the first month the value is =sum(Fields! Month_1.Value)*-1, and I
have named this text box 'MainRepM1'
The name of the main report is 'GMSHA Budget Adjustment Differentials'

The report displays both of the subreport and main report values
but I now need to total these values together for each month in order to
produce a grand total.

I have tried using the following to add the totals for Month 1 together,
=subreport1.Report.SubRepM1 + MainRepM1
but this does not work and I get the following error message €˜The value expression for the text box 'textbox18'contains an error [BC30451] Name subreport1 is not declared'.

I feel that it should be a simple matter of adding the two sets of values together but I€™m having major problems trying to get these totals to work.

Can anyone help, thanks

## Select DISTINCT On Multiple Columns Is Not Returning Distinct Rows?

Jul 6, 2007

Hi, I have the following script segment which is failing:

CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude))

INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache

When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."

Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.

The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.

Any ideas?

## Trying To Add A NON-DISTINCT Field To A DISTINCT Record Set In A Query.

Mar 12, 2007

I need to run a SELECT DISTINCT query acrossmultiple fields, but I need to add another field that is NON-DISTINCTto my record set.Here is my query:SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, genderFROM gpresultsWHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis query runs perfect. No problems whatsoever. However, I need toalso include another field called "admitdate" that should be treatedas NON-DISTINCT. How do I add this in to the query?I've tried this but doesn't work:SELECT admitdateFROM (SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, gender from gpresults)WHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis has to be simple but I do not know the syntax to accomplishthis.Thanks

## Total Spaceused And Total Allocated

Jul 20, 2005

Anyone has a "one sql statement" to get the total spaceused and totalspace allocated of an instance ? ie same as sum of relevance fieldsfrom sp_spaceused for each database in an instance, that works accrossversion of mssql from 6 onward.ThanksKD

## DISTINCT To ShortDateString, Not DISTINCT To The DateTime; How?

May 8, 2006

Hello,
I have written a small asp.net application, which keeps record of the proposals coming from the branch offices of a bank in a tableCREATEd as a TABLE Proposals ( ID smallint identity(7,1), BranchID char(5), Proposal_Date datetime )
This app also calculates the total number of proposals coming from a specific branch in a given date bySELECTing COUNT(BranchID) FROM Proposals WHERE BranchID=@prmBranchID AND Proposal_Date=@prmDateand prints them in a table (my target table).
This target table has as many rows as the result of  the "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals"and excluding the first column which displays those DISTINCT Proposal_Dates, it also has as many columns as the result of the"SELECT DISTINCT BranchID FROM Proposals". This target table converts the DateTime values ToShortDateString so that we are able to see comfortably which branch office has sent how many proposals in a given day.
So far so good, and everything works fine except one thing:
Certain DateTime values in the Proposals table which are of the same day but of different hours (for ex: 11.11.2005 08:30:45 and11.11.2005 10:45:30) cause some trouble in the target table,  where "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals" is executed, because (as you might already guess) it displays two identical dates in ShortDateString form, and this doesn't make much sense (i.e. it causes redundant rows)
What I need to do is to get a result like (in a neat fashion :)
"SELECT COUNT( DISTINCT Proposal_Date ) <<DISTINCT ONLY IN THE DAYS AND NOT IN HOURS OR MINUTES OR SECONDS>> FROM Proposals"
So, how to do it in a suitable way?

## Calculating The Total Amount Of Drugs Prescribed, Total Amount

Aug 10, 2006

Hi all,

I have a table named Prescription that consists of attributes like PatientId, MedicineCode, MedicineName, Prices of different drugs, quantity of different drugs(e.g 1,2,3,10), date .

I would like to get a summary of the total number and amount of different drugs in a specific period, the total amount of each type of drug.

I kindly request for help.

Ronnie

Feb 29, 2008

Hi,

I am trying to create a report on some data. I have about 8 tables and 30+ queries attached to those 15 reports. In one of those reports I want to get the percentage based on the data in the tables and queries. Say I have the minimum hours for an employee as 176 hours and the employee works for 227 hours in a month. I want to see the result in percentage.

My report looks something like this :

ID Name Oct Nov Dec Jan Feb March Total
001 alex 87.6% 104.1% 65.1% 50.2% 85.6%
002 Linda 87.4% 109.1% 68.1% 35.2% 90.8%
003 Jon 87.6% 104.1%
004 alex 87.6% 104.1% 65.1% 50.2% 85.6%
005 Linda 87.4% 109.1% 68.1% 35.2% 90.8%

For the 002 ID, though he has worked for Nov and Dec the total % is blank.

The formula that I used for all of these entries is :

=IIf(IsError([total]),"",[total])

and for the month it is : =IIf(IsError([Oct]),"",[oct]), nov and so on.

It works fine for all, but where ever there is blank in one field it doesn;t calculates for the others too..

Thanks,

Farn

## How To Show Distinct Rows Of The Column Of The Dataset And Number Of Distinct Rows Of That Column

Mar 29, 2007

suppose i have aDataset with 11 rows. field1 with 5 rows of aaa, 6 rows of "bbb"

I want's some thing like

field1 rowcount
aaa 5
bbb 6

## Total Sales By Customer Vs Total Sales

Nov 26, 2007

I have a report which totals sales by customer. Then table footer has a grand total of all customer sales. I would like to get a percent of each customer's sales against the total sales. How do I get the sum from the table footer to use in an individual customer row?

Thanks.

## Distinct Values Among Distinct Column Values

Jan 9, 2015

Okay, I've been working on this for a couple of hours with no success. I'm trying to find the number of telephone numbers that are associated with multiple students at different school sites. I've created a temp table that lists all phone numbers that are associated with more than one student. I'm now trying to query that table and count the number of telephone numbers that are associated with more than one site. Essentially, I'm looking for parent/guardians that have students at different sites.

Here's an example of what I'm hoping to accomplish:

*In this example, I'm just trying to get a count of the different/distinct school sites associated with each number. If I can, at the same time, limit it to a count of > 1 (essentially excluding parents with students at the same site), even better :)

===================================
Temp table
===================================
SCHOOL | STU_ID | STU_PHONE
101 | 12345 | 111-222-3333
101 | 23456 | 111-222-3333
102 | 34567 | 111-222-3333
101 | 45678 | 999-888-7777
101 | 56789 | 999-888-7777
101 | 67890 | 555-555-5555
102 | 78901 | 555-555-5555
103 | 89012 | 555-555-5555

==================================
Wanted query results
==================================
STU_PHONE | #Students | UNIQUE_SCHOOLS
111-222-3333 | 3 | 2
999-888-7777 | 2 | 1
555-555-5555 | 3 | 3

## Getting Total From Row

Mar 16, 2006

I have built a query in Access that calculates the total to be charged to clients based on 3 cost columns.

I have tried to run the query in MSSQL but it will not run, so could someone help me out with the syntax in my query.

I have done about half of it myself but I am stuck on the calculation and I havent a clue of the syntax to be used.

This is my Access Query;
SELECT DISTINCT Holiday_Bookings.ClientID,
Holiday_Bookings.Booking_Cost,
Room_Facilities.FacilityCost,
Rooms.CostPerNight, Rooms!CostPerNight*Nights_Stayed+Holiday_Bookings! Booking_Cost+Room_Facilities!FacilityCost AS TotalCost,
[TotalCost]*17.5/100+[TotalCost] AS [Total+VAT]
FROM Room_Facilities
INNER JOIN (Hotels INNER JOIN (Holiday_Bookings RIGHT JOIN Rooms ON Holiday_Bookings.ClientID = Rooms.ClientID)
ON Hotels.HotelID = Rooms.HotelID) ON Room_Facilities.FacilityID = Rooms.FacilityID;

and this is what I have managed to salvage in MSSQL format:
SELECT
Holiday_Bookings.ClientID,
Holiday_Bookings.Booking_Cost,
Rooms.CostPerNight,
Room_Facilities.FacilityCost
FROM
Rooms
INNER JOIN Holiday_Bookings ON (Rooms.Clients_ID = Holiday_Bookings.ClientID)
INNER JOIN Room_Facilities ON (Rooms.FacilityID = Room_Facilities.FacilityID)

Any help would be great as I am completely lost.

## Get A Total

Oct 19, 2006

Hello Everyone,

How can I get the total of the loan numbers:

USE CHEC
SELECT DISTINCT
DAT01.[_@051] AS Branch,
DAT01.[_@550] AS LoanType,
convert(varchar(10), DAT01.[_@040], 110) AS Date,
DAT01.[_@LOAN#] AS LoanNum
FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW]
ON DAT01.[_@040] = [_@040]
INNER JOIN [SMT_BRANCHES]
ON DAT01.[_@051] = SMT_BRANCHES.[BranchNbr]
WHERE
DAT01.[_@040] Between '06/01/2006' And '06/30/2006'
AND DAT01.[_@051] = '540'
And DAT01.[_@LOAN#] Like '2%'
And DAT01.[_@550] = '3'
GROUP BY
DAT01.[_@051],
DAT01.[_@550],
DAT01.[_@TP],
DAT01.[_@040],
DAT01.[_@LOAN#]
ORDER BY
DAT01.[_@051]

Where obviously DAT01.[_@LOAN#] is the LoanNum column.

TIA

Kurt

## Getting SUM Total

Jan 24, 2007

I have the following query which gives me a list of names, logins, and group name along with a count of how many training modules each individual has sat. This returns 70 rows with each individuals details and totals.

However I want to be able to report instead just the total modules sat for all 70 rows instead of displaying each row individually. This is the query I am using:

COUNT(PPS_SCOS.SCO_ID) AS coursecount
FROMPPS_PRINCIPALS
LEFT JOINPPS_TRANSCRIPTS ON PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID
AND PPS_TRANSCRIPTS.STATUS like '[PCF]'
AND PPS_TRANSCRIPTS.TICKET not like 'l-%'
AND pps_transcripts.date_created between '2006-10-01' and '2007-09-30'
LEFT JOINPPS_SCOS ON PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_ID
AND pps_scos.name like 'MT%'
WHERE pps_principals.login like '%score%' and ext_user_groups.name like 'ALL SCORE PTY'
AND dbo.PPS_PRINCIPALS.DISABLED IS NULL
HAVING COUNT(PPS_SCOS.SCO_ID) > 0

I am trying to use SUM to get the overall total but without success so far.

## Sum (Total)

May 22, 2007

I need to sum the totals for each of the ratings, then group them by owneridname. the problem i am having is that the "Count(*) as total" returns a total that includes ratings that are not listed below as a criteria.

Eg. the total for all ratings for a user john would be 789.

SELECT owneridname, SUM(CASE WHEN new_ratingname = 'Hot' THEN 1 ELSE 0 END) AS Hot,
SUM(CASE WHEN new_ratingname = 'warm' THEN 1 ELSE 0 END) AS Warm, SUM(CASE WHEN new_ratingname = 'cold' THEN 1 ELSE 0 END) AS Cold,
SUM(CASE WHEN new_ratingname = 'cool' THEN 1 ELSE 0 END) AS Cool, SUM(CASE WHEN new_ratingname = 'new' THEN 1 ELSE 0 END) AS New,
SUM(CASE WHEN new_ratingname = 'Reservation Holder' THEN 1 ELSE 0 END) AS [Reservation Holder],
SUM(CASE WHEN new_ratingname = 'site visit' THEN 1 ELSE 0 END) AS [Site Visit], SUM(CASE WHEN new_ratingname IS NULL THEN 1 ELSE 0 END)
AS [Not Rated], SUM(CASE WHEN new_ratingname = 'Continous Updates' THEN 1 ELSE 0 END) AS [Cont Updates],
SUM(CASE WHEN new_ratingname = 'worked tasks' THEN 1 ELSE 0 END) AS [Wkd Tasks], COUNT(*) AS Total
FROM FilteredContact AS filteredcontact
WHERE (statuscodename = 'active')
GROUP BY owneridname

Melvin Felicien
IT Manager
DCG Properties Limited

## Help With Total

Oct 29, 2007

HI everyone, I been looking for the answer for sometime now, thought i would post and see if anyone could help me it. This is the output of my query.

F_DivDiv_4Div_5Div_35
424600
504000
3510114

After the row 35 i want to add a row for total. I want to add the values of column Div_4 so it would be 246+0+1. The problem i'm having is that the columns are populated from using this logic.

Select
rr.F_BVT_DIV_NO,
(Case When rr3.F_DIVISION_NO = 4 then Max(rr3.Total_Pol_Count) else 0 end ) + (MAX(isnull(rr2.Div4,0))) Div_4

from RNEWL_RTENTN_DETL RR
--join for division 4 transfers

left join(

select
rr2.F_BVT_DIV_NO,
count(rr2.X_POLICY_NO) As Div4
from RNEWL_RTENTN_DETL RR2
where
RR2.F_BVT_DIV_NO<>RR2.F_DIVISION_NO
and month(RR2.X_POLICY_XPRTN_DT)=2 and
year(RR2.X_POLICY_XPRTN_DT)=2007 and
rr2.F_DIVISION_NO = 4
Group By
rr2.F_BVT_DIV_NO ) rr2 on
rr2.F_BVT_DIV_NO = rr.F_BVT_DIV_NO

from TRNEWL_RTENTN_DETL rr3
where
month(rr3.X_POLICY_XPRTN_DT)=2 and
year(rr3.X_POLICY_XPRTN_DT)=2007
Group by
rr3.F_DIVISION_NO) rr3 on
rr3.F_DIVISION_NO = rr.F_BVT_DIV_NO
group by
rr.F_BVT_DIV_NO,
rr3.F_DIVISION_NO
order by
rr.F_BVT_DIV_NO

That code is just a little piece of it, but the logic is the same throughout. Thanks for the input!

## Getting A Total From A Total

Mar 29, 2007

basically I need to sum the value of one group from another group.

Is this possible yet?

## % Of Total

Aug 28, 2007

I have a matrix report built on a database not on the cube.

2006
Q1

Volume MS %
Sub Channel Product
X X1 10

X2 20
Subtotal 30
Y Y1 20
Y2 20

Subtotal 40

I need to calculate the Market Share values which are Volume of the product / Subtotal for example

MS% of Product X1 = Volume of X1 / Subtotal i.e 10/ 30 * 100 = 33.33 %

How can i achieve this within the report..Can somebody help/advise...

## Sub Sub Total

Jan 22, 2007

OK basically what I'm looking for is how to have two subtotals in a report along with a Grand Total.

So it would be something like this:

Revenue

Subtotal

Expense

Subtotal

Grand Total.

The grand total works fine, but I'm not sure how to add the subtotals.

## Query To Get Total

Feb 12, 2008

Hey all,
I have following query

select Date,

## Getting Total Of Different Records

Mar 13, 2008

I have a SQL data source and i would like to present the total number of different records based on a "status" field.
I have done total records in the past by doing this:
protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)    {        int RecordCount = e.AffectedRows;            if (RecordCount == 1)            { litRecordCount.Text = "1 record found"; }            else            { litRecordCount.Text = RecordCount.ToString() + " records found"; }        }
This would work, howerver, my SQLdatasource choose * records, and not based on a WHERE condition. Is there any way to total just those with a status of "Initialized" ?
I tried to do it on the Gridview, but then I realized if I have paging on, it will only be on that front page.
thoughts?

## SQL With A Cummlative Total

Apr 14, 2005

Hello all SQL guru's

Is it possible to write an sql statement that will calculate a cummlative total of another field in the sql like below. The BOS column is being calculated an the sql and then the *** column is adding them up.

Thanks, Gary

VBS_NO
DIV_NO
ITM_NO
SLL_DLR
BOS
***%

609
9
53910
\$          36,270,045
1.24%
1.24%

609
9
53985
\$          31,542,973
1.08%
2.33%

609
9
16870
\$          29,583,159
1.01%
3.34%

609
9
24883
\$          29,532,282
1.01%
4.35%

609
9
39674
\$          27,856,172
0.96%
5.31%

609
9
11485
\$          27,598,356
0.95%
6.26%

609
9
39676
\$          26,933,126
0.92%
7.18%

609
9
33354
\$          23,056,438
0.79%
7.97%