grand total - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

grand total

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-09-11 : 04:00:48

Hey guys

I need some advice

i have created the following query which produces four columns. I am not sure if this is possible, but after the very last row/record, i would like to sum up the whole totalofnetamount and
TotalofNetTransactions columns

this is my query

Declare @date varchar(10)
set @Date = (select dateadd(MM,-2,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_History)

SELECT
[dbo].[Dim_Outlet].FDMSAccountNo_First9,
[dbo].[Dim_Outlet].DBA_Name,
SUM(dbo.Fact_Financial_History.Net_Sales) AS TotalofNetAmount,
SUM(dbo.Fact_Financial_History.Net_Trans)AS TotalofNetTransactions
FROM Fact_Financial_History INNER JOIN
Dim_Outlet ON Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
INNER JOIN dbo.Dim_MCC ON dbo.Dim_Outlet.MCC_Code = dbo.Dim_MCC.MCC
WHERE (dbo.Fact_Financial_History.hst_prod_code BETWEEN '79' AND '84')
and MCC_Code = '5968'
AND (dbo.Fact_Financial_History.hst_date_processed > @date)
group by [dbo].[Dim_Outlet].DBA_Name,
[dbo].[Dim_Outlet].FDMSAccountNo_First9
order by [dbo].[Dim_Outlet].DBA_Name asc

desired layout for example

FDMSAccountNo_First9, DBA_Name, totalofnetamount TotalofNetTransactions

111111111 1a 1000 2000
111111112 1b 2000 4000
111111113 1c 3000 6000
111111114 1d 4000 8000

total 10000 20000

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2012-09-11 : 04:38:03

I asked a similar question recently if that helps any but I was using a pivot:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=175001

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-09-11 : 07:51:13

hi grifter thank you for your response, Unfort your situation, doesnt seem to apply my case

Thank you for postin you problem though gave me a huge insight on what i need to do on another query though :)

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-09-11 : 08:11:02

try adding WITH ROLLUP

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-09-11 : 08:24:34

Hi Donatwork

Do you have an example of a rollup ?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-09-11 : 08:50:09

sure.



create table #AMT_TBL (rqst_amount money, sec_req money, OFFICE_ID int)
insert into  #AMT_TBL 
	values (12.34,23.56,1)
	,(436.32,346.32,1)
	,(3246.64,4367.54,2)
	,(457.00,437.46, 2)

select 
	SUM(rqst_amount), sum(sec_req),OFFICE_ID 
from 
	#AMT_TBL 
where 
	OFFICE_ID in(1,2)
group by
	OFFICE_ID 
with rollup 

drop table #amt_tbl


sum1            sum2    	OFFICE_ID
448.66	         369.88	        1
3703.64	         4805.00	2
4152.30	         5174.88	NULL

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-09-11 : 08:55:18

Donatwork

I found an example and used that :)

Thank you very much for your help and assistance

Btw do you know if you can name the rollup

for example it produces a line called null and the sum

Can you change null to total ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 11:10:07

[code]

create table #AMT_TBL (rqst_amount money, sec_req money, OFFICE_ID int)
insert into #AMT_TBL
values (12.34,23.56,1)
,(436.32,346.32,1)
,(3246.64,4367.54,2)
,(457.00,437.46, 2)

select
SUM(rqst_amount), sum(sec_req),COALESCE(CAST(OFFICE_ID AS varchar(10)),'Total')
from
#AMT_TBL
where
OFFICE_ID in(1,2)
group by
OFFICE_ID
with rollup

drop table #amt_tbl
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-09-11 : 11:41:37

hi visakh16

my query is

Declare @date varchar(10)
set @Date = (select dateadd(MM,-2,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_History)

SELECT
[dbo].[Dim_Outlet].FDMSAccountNo_First9,
--[dbo].[Dim_Outlet].DBA_Name,
SUM(dbo.Fact_Financial_History.Net_Sales) AS TotalofNetAmount,
SUM(dbo.Fact_Financial_History.Net_Trans)AS TotalofNetTransactions
FROM Fact_Financial_History INNER JOIN
Dim_Outlet ON Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
INNER JOIN dbo.Dim_MCC ON dbo.Dim_Outlet.MCC_Code = dbo.Dim_MCC.MCC
WHERE (dbo.Fact_Financial_History.hst_prod_code BETWEEN '79' AND '84')
and MCC_Code = '5968'
AND (dbo.Fact_Financial_History.hst_date_processed > @date)
group by
--[dbo].[Dim_Outlet].DBA_Name,
[dbo].[Dim_Outlet].FDMSAccountNo_First9
with rollup

it returns the following results
FDMSAccountNo_First9 ,TotalofNetAmount, TotalofNetTransactions

1111111 1000 2000
1111112 2000 2000
1111113 3000 2000
nul 6000 6000

How can i change the null to say (Grand Total)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-11 : 13:27:54

Here is one way using a CASE expression and the GROUPING function:

SELECT 
    CASE 
        WHEN GROUPING([dbo].[Dim_Outlet].FDMSAccountNo_First9) = 1 THEN 'Grand Total'
        ELSE CAST([dbo].[Dim_Outlet].FDMSAccountNo_First9 AS VARCHAR(20))
    END AS FDMSAccountNo_First9,
    SUM(dbo.Fact_Financial_History.Net_Sales) AS TotalofNetAmount, 
    SUM(dbo.Fact_Financial_History.Net_Trans)AS TotalofNetTransactions
FROM 
    Fact_Financial_History 
INNER JOIN
    Dim_Outlet 
    ON Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
INNER JOIN 
    dbo.Dim_MCC 
    ON dbo.Dim_Outlet.MCC_Code = dbo.Dim_MCC.MCC
WHERE 
    (dbo.Fact_Financial_History.hst_prod_code BETWEEN '79' AND '84')
    and MCC_Code = '5968' 
    AND (dbo.Fact_Financial_History.hst_date_processed > @date)
group by 
    [dbo].[Dim_Outlet].FDMSAccountNo_First9
with rollup

EDIT: Cut-npaste error.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 14:16:56

quote:
Originally posted by masond

hi visakh16

my query is

Declare @date varchar(10)
set @Date = (select dateadd(MM,-2,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_History)

SELECT
[dbo].[Dim_Outlet].FDMSAccountNo_First9,
--[dbo].[Dim_Outlet].DBA_Name,
SUM(dbo.Fact_Financial_History.Net_Sales) AS TotalofNetAmount,
SUM(dbo.Fact_Financial_History.Net_Trans)AS TotalofNetTransactions
FROM Fact_Financial_History INNER JOIN
Dim_Outlet ON Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
INNER JOIN dbo.Dim_MCC ON dbo.Dim_Outlet.MCC_Code = dbo.Dim_MCC.MCC
WHERE (dbo.Fact_Financial_History.hst_prod_code BETWEEN '79' AND '84')
and MCC_Code = '5968'
AND (dbo.Fact_Financial_History.hst_date_processed > @date)
group by
--[dbo].[Dim_Outlet].DBA_Name,
[dbo].[Dim_Outlet].FDMSAccountNo_First9
with rollup

it returns the following results
FDMSAccountNo_First9 ,TotalofNetAmount, TotalofNetTransactions

1111111 1000 2000
1111112 2000 2000
1111113 3000 2000
nul 6000 6000

How can i change the null to say (Grand Total)

you can apply similar logic here too

else use Lamprey suggestion to do CASE WHEN based on GROUPING_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-09-11 : 15:53:42

hi lamprey and visakh16

if to produce the following results as
DBA_Name, FDMSAccountNo_First9 ,TotalofNetAmount, TotalofNetTransactions

abc1 1111111 1000 2000
abc2 1111112 2000 2000
abc3 1111113 3000 2000
grand total 6000 6000

would the query be

SELECT
CASE
WHEN GROUPING([dbo].[Dim_Outlet].FDMSAccountNo_First9) = 1 THEN 'Grand Total'
ELSE CAST([dbo].[Dim_Outlet].FDMSAccountNo_First9 AS VARCHAR(20))
else cast [dbo].[Dim_Outlet].DBA_Name as varchar(20))
END AS FDMSAccountNo_First9,dba_name
SUM(dbo.Fact_Financial_History.Net_Sales) AS TotalofNetAmount,
SUM(dbo.Fact_Financial_History.Net_Trans)AS TotalofNetTransactions
FROM
Fact_Financial_History
INNER JOIN
Dim_Outlet
ON Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
INNER JOIN
dbo.Dim_MCC
ON dbo.Dim_Outlet.MCC_Code = dbo.Dim_MCC.MCC
WHERE
(dbo.Fact_Financial_History.hst_prod_code BETWEEN '79' AND '84')
and MCC_Code = '5968'
AND (dbo.Fact_Financial_History.hst_date_processed > @date)
group by
[dbo].[Dim_Outlet].FDMSAccountNo_First9,-[dbo].[Dim_Outlet].DBA_Name
with rollup

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 15:57:09

first run select itself and see what GROUPING_ID returns for each of the total rows

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources