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 query, whats the best way?

I have a query that must return information for an invoice. It is not straight forward though. Some things need to be displayed from the if something else is happening...

For example...

declare @.idintdeclare @.chargeidintset @.id = 1set @.chargeid = 9declare @.ppbit, @.pagesint, @.pagefeemoney, @.pagechargemoneyset @.pp = (select perpagefrom tblmainfeeswhere mainfeesid = @.chargeid)if (@.pp = 1)beginset @.pages = (select pagesfrom tblchargesinnerjoin tblrequeston requestid = fkrequestidwhere requestid = @.id)if (@.pages > 0)beginset @.pagefee = (select perpagefeefrom tblmainfeeswhere mainfeesid = @.chargeid)set @.pagecharge = (@.pages * @.pagefee)endend...select @.pagechargeas PerPageCharge

Or should I do some complex query with only one select?

Its hard for anyone to understand if your question is like this: "Some things need to be displayed from the if something else is happening..."

Give us some sample data and your query and what you are expecting to see out of it.

|||

Your right, sorry about that. I slid that one in quick before a meeting...

I will try to explain better.

I have stored procedure that returns data for an invoice, and because Ihate Crystal Reports, I do the most work I can in the stored procedure (formating the text, etc...).

The current query I have involves a 2 complex queries with tons of nested case statements. It is hard to read and does not flow nicely at all, but it works.

I just made enough changes to the database that the stored procedure needs to be rewritten.

I took this opportunity to try and clean up what I wrote prior. I posted a small expample above.

To me, I made it almost "VB" like. Instead of complex select statements with nested logic, I made a variable for each item I needed, and some extra variables for logic. I set each of these variables with it's own select statement, if they passed the logic i had layed out (most of them were initialized with a value). Again, to me, I envisioned each variable being set like a property or a function in VB. I ended up having over 15 simple selects to set variables, and one select at the end to return (get) all of the variables.

The code ended up being much easier to read, and I cut the original code in half, but I greatly increased the amount of select statements. I did some time statistics to see which was faster, and the new query ended up being twice as fast, doing the same work.

My question: Is the way I described and used an accepted way practice? Did I make it some what clearer of what I did or am trying to do?

|||

I would post some data and examples, but I don't think it's relevant. I am not looking for how to do something, but the best way to do something.

For example: "Are you stupid? Don't do it like that." or "Yeah, that's fine."

|||How did you measure the statistics? Just by looking at the time in the query analyzer window? Check out some of the SET STATISTICS IO ON and SET STATISTICS TIME ON statements in books on line. then do the compare for before and after. If you are more comfortable with Profiler, get the aggregate reads on each of the tables. See which method requires less reads. From what you described it looks like you are reading the same tables more than once for each of those variables. I would get all the values you need into variables in as many few SELECTs as possible. Then work on the variables with all the formatting. Again, formatting is better done at the reporting layer. I think you use VB code in crystal reports right? So if you are more comfortable in VB you might want to move the formatting to the CR layer.