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:
>

Complicated Query with Subs for Grand Total

I'm trying to get the syntax right below. Know that what I'm trying to do is correct (I don't want to explain why or the entire thing but just need help with correcting the syntax issues on the join and stuff:

Errors:

Msg 156, Level 15, State 1, Procedure IT_Get_ProjFee_GT, Line 205

Incorrect syntax near the keyword 'INNER'.

Msg 156, Level 15, State 1, Procedure IT_Get_ProjFee_GT, Line 213

Incorrect syntax near the keyword 'as'.

Msg 170, Level 15, State 1, Procedure IT_Get_ProjFee_GT, Line 219

Line 219: Incorrect syntax near 'branch'.

Msg 170, Level 15, State 1, Procedure IT_Get_ProjFee_GT, Line 219

Line 219: Incorrect syntax near ')'.

Msg 170, Level 15, State 1, Procedure IT_Get_ProjFee_GT, Line 221

Line 221: Incorrect syntax near ')'.

Select @.InHouse2 = SUM(InHouse2)

FROM

(Select SUM (CCsNewTable.SumOldCCs) * CASE WHEN FeeSchedule = 0 THEN

1

WHEN FeeSchedule = 1 THEN

FeeSchedule / 100

ELSE

FeeSchedule / 100

END AS InHouse2

FROM DC

INNER JOIN

( Select Company, FeeSchedule, SUM(OldCCs) SumOldCCs

INNER JOIN

(Select CC AS OldCCs from DC d

WHERE d.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today)) AND d.EnteredDate IS NOT NULL

AND Type = 'CC'

AND ((branch = '00002' and customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1) )

OR (customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0) ))

AND pdc_OnHold IS NULL

GROUP BY CompanyNumber

) as CCsNewTable ON CompanyNumber = CCsNewTable.CompanyNumber

) as InHouse2Table

WHERE branch='00002'

AND Type = 'In-House'

AND ((branch = '00002' and customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

)

Also tried adding aliases to distinguish the difference between DC and DC with d and dd

Select @.InHouse2 = SUM(InHouse2)

FROM

(Select SUM (CCsNewTable.SumOldCCs) * CASE WHEN FeeSchedule = 0 THEN

1

WHEN FeeSchedule = 1 THEN

FeeSchedule / 100

ELSE

FeeSchedule / 100

END AS InHouse2

FROM DCR d

INNER JOIN

( Select Company, FeeSchedule, SUM(OldCCs) SumOldCCs

INNER JOIN

(Select CC AS OldCCs from DCR dd

WHERE dd.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today)) AND dd.EnteredDate IS NOT NULL

AND dd.Type = 'CC'

AND ((dd.branch = '00002' and dd.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1) )

OR (dd.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0) ))

AND dd.pdc_OnHold IS NULL

GROUP BY dd.CompanyNumber

) as CCsNewTable ON d.CompanyNumber = CCsNewTable.CompanyNumber

) as InHouse2Table

WHERE d.branch='00002'

AND d.Type = 'In-House'

AND ((d.branch = '00002' and d.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

)

|||You need to specify the JOIN conditions wiht your ON statement after the end of the subquery you are joining to, as I did in the part of the sub query:

as CCsNewTable ON CompanyNumber = CCsNewTable.CompanyNumber

HTH
|||

My aliases and other stuff are still screwed up...need some help on this one...I can't see it:

DECLARE @.today DATETIME,

@.datefirst datetime,

@.Posted money,

@.GrossGoal money,

@.OldPDCs money,

@.NewPDCs money,

@.OldCCs money,

@.NewCCs money,

@.InHouse1 money,

@.InHouse2 money,

@.Var1 money

SET @.today = CONVERT(CHAR(8), GETDATE(), 112) --'Jun 26 2005'

SET @.datefirst = @.@.DATEFIRST

Select @.InHouse2 = SUM(inhouse1table.InHouse2)

FROM

(Select SUM (InHouse2Table.InHouse2) * CASE WHEN FeeSchedule = 0 THEN

1

WHEN FeeSchedule = 1 THEN

FeeSchedule / 100

ELSE

FeeSchedule / 100

END AS InHouse2

FROM DC d

INNER JOIN

( Select branch, Type, CustomerNumber, CustomerName, SUM(OldCCs) * FeeSchedule as InHouse2

FROM DC ddd

INNER JOIN

(Select CustomerNumber, CC AS OldCCs from DC dd

WHERE dd.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today)) AND dd.EnteredDate IS NOT NULL

AND dd.Type = 'CC'

AND ((dd.branch = '00002' and dd.CustomerNumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1) )

OR (dd.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

AND dd.pdc_OnHold IS NULL

GROUP BY dd.CustomerNumber

)as CCsNewTable ON CompanyNumber = CCsNewTable.CompanyNumber

) as InHouse2Table on d.CustomerNumber = inhouse2table.CustomerNumber

WHERE d.branch='00002'

AND d.Type = 'In-House'

AND ((d.branch = '00002' and d.CustomerNumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

) as inhouse1table

|||

Please check out another post here which explains what I'm doing which was one step before this post:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21753502.html

|||Is this a college assignment or something? Wh are you asking the same questions as dba123? If its for school, I cannot help you.|||are you out of your mind? does this look like a college assignement? something this complicated? What do you mean same questions...this is not the same. Is this too complicated for you to help me with? This is Grand Total that I have to mimic from a crazy excel spreadsheet from our finance dept....|||

this is a complete different problem than you saw from dba123...if you read you'll realize this. Just check this out, OK then come back with some useful replies please:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21753502.html

|||

that's ok pal, I fixed it...

DECLARE @.today DATETIME,
@.datefirst datetime,
@.Posted money,
@.GrossGoal money,
@.OldPDCs money,
@.NewPDCs money,
@.OldCCs money,
@.NewCCs money,
@.InHouse1 money,
@.InHouse2 money,
@.Var1 money

SET @.today = CONVERT(CHAR(8), GETDATE(), 112) --'Jun 26 2005'
SET @.datefirst = @.@.DATEFIRST

Select @.InHouse2 = SUM(InHouse2)
FROM
(Select CCsOldTable.SumOldCCs * CASE WHEN d1.FeeSchedule = 0 THEN
1
WHEN d1.FeeSchedule = 1 THEN
(d1.FeeSchedule / 100)
ELSE
(FeeSchedule / 100)
END AS InHouse2
FROM DCR d1
INNER JOIN
(Select d2.CustomerNumber, SUM(CCsQuery.OldCCs) AS SumOldCCs
FROM DCR d2
INNER JOIN
-- NewCCs Query
(Select CustomerNumber, CC AS OldCCs
FROM DCR d3
WHERE d3.EnteredDate NOT BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND d3.Type = 'CC'
AND ((d3.branch = '00002' and d3.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))
OR (d3.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))
AND d3.dc_OnHoldDate IS NULL
GROUP BY d3.CustomerNumber, d3.CC)as CCsQuery on CCsQuery.CustomerNumber = d2.CustomerNumber
GROUP BY d2.CustomerNumber
) as CCsOldTable ON d1.CustomerNumber = CCsOldTable.CustomerNumber

GROUP BY d1.CustomerNumber, CCsOldTable.SumOldCCs, d1.FeeSchedule
) as z

Select @.InHouse2

|||

Sorry, did not mean to offend you, I was just wondering why you are using the same tables and queries. Hey, some college courses can get complicated now

Hope I was some help. Btw, you would probably be better (and quicker) to use a single join with an OR here instead of using IN, which is slow:

AND ((d3.branch = '00002' and d3.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))
OR (d3.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

|||

thanks, sorry, yea, I was a bit irritated but that's cool.

Can you help me with your explanation ion using the single join with OR instead of IN ?

|||I figured it out....|||Sorry, that was the last thing I posted before logging off.