Computing a Grand Total

I have inherited a table that has entries such as:
AccountName, [Invoice No], [Sales Price] Qty, etc...
I would like to have a SubTotal and GrandTotal. So, I have a select
statement something like this:
Select AccountName, Qty, [Invoice No], [Sales Price], [Sales Price] * Qty as
SubTotal
FROM ...
That works fine, I get an extra column with the product of Qty and Sales
Price.
However, how would I get a Grand Total per invoice? I can do it in VB.Net,
but would like to see how to do it in SQL.
TIA,
PaoloCOMPUTE SUM(SubTotal) BY [Invoice No]
Here's the Books ONline URL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/69009df2-dba5-4bcb-b2ae-
a7502537cb3e.htm
HTH. Ryan
"Paul" <PaulContactMe@.TheCornerStore.com> wrote in message
news:LesBf.1078$v81.173@.fe12.lga...
>I have inherited a table that has entries such as:
> AccountName, [Invoice No], [Sales Price] Qty, etc...
> I would like to have a SubTotal and GrandTotal. So, I have a select
> statement something like this:
> Select AccountName, Qty, [Invoice No], [Sales Price], [Sales Price] * Qty
> as SubTotal
> FROM ...
> That works fine, I get an extra column with the product of Qty and Sales
> Price.
> However, how would I get a Grand Total per invoice? I can do it in VB.Net,
> but would like to see how to do it in SQL.
> TIA,
> Paolo
>
>|||Try
SELECT [Invoice No], SUM([Sales Price] * Qty)
FROM [YourTable]
GROUP BY [Invoice No]
If you want grand totals for AccountName and [Invoice No]
SELECT AccountName, [Invoice No], SUM([Sales Price] * Qty) AS "Total"
FROM [YourTable]
GROUP BY AccountName, [Invoice No]
WITH ROLLUP
The out put of the above query will have rows like
AccountName [Invoice No] Total
---
Some Account NULL $3.50
A row with a NULL in the [Invoice No] column indicates the grand total
for that AccountName. You will also get a row like
AccountName [Invoice No] Total
---
NULL NULL $1500.78
which shows the total for all invoices and accounts.
"Paul" wrote:

> I have inherited a table that has entries such as:
> AccountName, [Invoice No], [Sales Price] Qty, etc...
> I would like to have a SubTotal and GrandTotal. So, I have a select
> statement something like this:
> Select AccountName, Qty, [Invoice No], [Sales Price], [Sales Price] * Qty
as
> SubTotal
> FROM ...
> That works fine, I get an extra column with the product of Qty and Sales
> Price.
> However, how would I get a Grand Total per invoice? I can do it in VB.Net,
> but would like to see how to do it in SQL.
> TIA,
> Paolo
>
>|||Try following examples on pubs database. You will have to use COMPUTE clause
.
use pubs
go
select ord_num, stor_id, qty from sales
order by ord_num
compute sum(qty) by ord_num
--using computed expression query will look like.
select ord_num, stor_id, (qty * 2) as subtotal from sales
order by ord_num
compute sum(qty * 2) by ord_num
"Paul" wrote:

> I have inherited a table that has entries such as:
> AccountName, [Invoice No], [Sales Price] Qty, etc...
> I would like to have a SubTotal and GrandTotal. So, I have a select
> statement something like this:
> Select AccountName, Qty, [Invoice No], [Sales Price], [Sales Price] * Qty
as
> SubTotal
> FROM ...
> That works fine, I get an extra column with the product of Qty and Sales
> Price.
> However, how would I get a Grand Total per invoice? I can do it in VB.Net,
> but would like to see how to do it in SQL.
> TIA,
> Paolo
>
>|||I agree with Mark. Using ROLLUP on a GROUP BY is the far better choice,
since it will always be returned in one result set. COMPUTE is pretty ugly
to deal with for the user program.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:799AF000-43FC-483A-94E1-D5DFC3FA3CEF@.microsoft.com...
> Try
> SELECT [Invoice No], SUM([Sales Price] * Qty)
> FROM [YourTable]
> GROUP BY [Invoice No]
> If you want grand totals for AccountName and [Invoice No]
> SELECT AccountName, [Invoice No], SUM([Sales Price] * Qty) AS "Total"
> FROM [YourTable]
> GROUP BY AccountName, [Invoice No]
> WITH ROLLUP
> The out put of the above query will have rows like
> AccountName [Invoice No] Total
> ---
> Some Account NULL $3.50
> A row with a NULL in the [Invoice No] column indicates the grand total
> for that AccountName. You will also get a row like
>
> AccountName [Invoice No] Total
> ---
> NULL NULL $1500.78
> which shows the total for all invoices and accounts.
>
> --
> "Paul" wrote:
>

Complex expression in matrix subtotal

Hello.
How can I make an expression in matrix subtotal as (sum1) & "/" & (sum2)?
Thank's.=Sum(Fields!Field1.Value) / Sum(Fields!Field2.Value)
If you want something other than this in the detail cells, you'll need to
use the InScope function. Something like this:
=iif(InScope("matrix1_Category"),Sum(Fields!Amount.Value),Sum(Fields!Amount.
Value)/Sum(Fields.ItemCount.Value))
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Efim" <Efim@.discussions.microsoft.com> wrote in message
news:587C7384-4DC7-492F-84C5-CD895512FAC9@.microsoft.com...
> Hello.
> How can I make an expression in matrix subtotal as (sum1) & "/" & (sum2)?
> Thank's.|||Thanks.
"Chris Hays [MSFT]" wrote:
> =Sum(Fields!Field1.Value) / Sum(Fields!Field2.Value)
> If you want something other than this in the detail cells, you'll need to
> use the InScope function. Something like this:
> =iif(InScope("matrix1_Category"),Sum(Fields!Amount.Value),Sum(Fields!Amount.
> Value)/Sum(Fields.ItemCount.Value))
> --
> My employer's lawyers require me to say:
> "This posting is provided 'AS IS' with no warranties, and confers no
> rights."|||As indicated by Chris, you would use the InScope function. The subtotal
column is not in the scope of the detail columns of the group. E.g.
=iif(InScope("matrix1_Category"), Sum(Fields!Amount.Value),
Avg(Fields!Amount.Value))
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shai" <Shai@.discussions.microsoft.com> wrote in message
news:E2641A91-9CAF-4C01-8693-A5BB142CCD29@.microsoft.com...
> Hi Chris !
> When you add subtotal to matrix it's add a column but you don't have
access to the expression of the actual calculation. I would like my "Total
column" to calculate Average of the columns on the left. How is this
possible?
> TIA
> Shai
> "Chris Hays [MSFT]" wrote:
> > =Sum(Fields!Field1.Value) / Sum(Fields!Field2.Value)
> >
> > If you want something other than this in the detail cells, you'll need
to
> > use the InScope function. Something like this:
> >
> >
=iif(InScope("matrix1_Category"),Sum(Fields!Amount.Value),Sum(Fields!Amount.
> > Value)/Sum(Fields.ItemCount.Value))
> >
> > --
> > My employer's lawyers require me to say:
> > "This posting is provided 'AS IS' with no warranties, and confers no
> > rights."
> >
> > "Efim" <Efim@.discussions.microsoft.com> wrote in message
> > news:587C7384-4DC7-492F-84C5-CD895512FAC9@.microsoft.com...
> > > Hello.
> > > How can I make an expression in matrix subtotal as (sum1) & "/" &
(sum2)?
> > > Thank's.
> >
> >
> >|||Hi Robert,
I tried this option and i am getting #Error in the data column.Moreover i am
getting the below error on the preview of the report.
Aggregate functions other than First, Last, Previous, Count, and
CountDistinct can only aggregate data of a single data type.
--
Regards,
Raja
â'Smile is a curve that sets everything straight"
"Robert Bruckner [MSFT]" wrote:
> As indicated by Chris, you would use the InScope function. The subtotal
> column is not in the scope of the detail columns of the group. E.g.
> =iif(InScope("matrix1_Category"), Sum(Fields!Amount.Value),
> Avg(Fields!Amount.Value))
> See also:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Shai" <Shai@.discussions.microsoft.com> wrote in message
> news:E2641A91-9CAF-4C01-8693-A5BB142CCD29@.microsoft.com...
> > Hi Chris !
> >
> > When you add subtotal to matrix it's add a column but you don't have
> access to the expression of the actual calculation. I would like my "Total
> column" to calculate Average of the columns on the left. How is this
> possible?
> >
> > TIA
> > Shai
> >
> > "Chris Hays [MSFT]" wrote:
> >
> > > =Sum(Fields!Field1.Value) / Sum(Fields!Field2.Value)
> > >
> > > If you want something other than this in the detail cells, you'll need
> to
> > > use the InScope function. Something like this:
> > >
> > >
> =iif(InScope("matrix1_Category"),Sum(Fields!Amount.Value),Sum(Fields!Amount.
> > > Value)/Sum(Fields.ItemCount.Value))
> > >
> > > --
> > > My employer's lawyers require me to say:
> > > "This posting is provided 'AS IS' with no warranties, and confers no
> > > rights."
> > >
> > > "Efim" <Efim@.discussions.microsoft.com> wrote in message
> > > news:587C7384-4DC7-492F-84C5-CD895512FAC9@.microsoft.com...
> > > > Hello.
> > > > How can I make an expression in matrix subtotal as (sum1) & "/" &
> (sum2)?
> > > > Thank's.
> > >
> > >
> > >
>
>