Solved

# Need an extra row to show the total for each column.

Posted on 2011-10-19
309 Views
IN SQL, I need an extra row at the very bottom to show the total for each column in order to get a summary for each row.  How do I do that?  Below is my query.

``````--Page 1.5: My Financials – Accounts Receivable

--Still needs grand total of each column

Declare @Attorney varchar(5),
@Type varchar(10);  --(B) Billing or (W) Working

SET @Attorney = '02500'
SET @Type = 'B'

IF @Type = 'W'--WORKING
BEGIN
select tkinit as WA_ID, tklast+', ' + tkfirst  as WA_name,
clnum as Client_id,
clname1 as Client_name,
mmatter as Matter_id,
mdesc1 as Matter_name,
convert(decimal(11,2),round (sum(arfbal+arcbal),2)) AS Total_AR,
convert(decimal(11,2),round (sum(arf1+arc1),2)) as AR_0_30,
convert(decimal(11,2),round (sum(arf2+arc2),2)) as AR_31_60,
convert(decimal(11,2),round (sum(arf3+arc3),2)) AS AR_61_90,
convert(decimal(11,2),round (sum(udf19+udf20),2)) as AR_91_120,
convert(decimal(11,2),round (sum((arf4+arc4)-(udf19+udf20)),2)) as AR_121_180,
convert(decimal(11,2),round (sum(arf5+arc5),2)) as AR_181_365,
convert(decimal(11,2),round (sum(arf6+arc6),2)) as AR_over_365
from df_dwtkmat, df_dwcontrol, timekeep, matter, client
where matter=mmatter
and mclient=clnum
and watty=tkinit
and rectype=4020
and pe=defvalue
and watty=@Attorney
group by tkinit, tklast+', ' + tkfirst,
clnum,
clname1,
mmatter,
mdesc1
having sum(arfbal+arcbal)<>0
order by tkinit, tklast+', ' + tkfirst,
clnum,
clname1,
mmatter,
mdesc1
END
ELSE
BEGIN --by Billing Partner
select tkinit as BP_ID, tklast+', ' + tkfirst  as BP_name,
clnum as Client_id,
clname1 as Client_name,
mmatter as Matter_id,
mdesc1 as Matter_name,
convert(decimal(11,2),round (sum(arfbal+arcbal),2)) AS Total_AR,
convert(decimal(11,2),round (sum(arf1+arc1),2)) as AR_0_30,
convert(decimal(11,2),round (sum(arf2+arc2),2)) as AR_31_60,
convert(decimal(11,2),round (sum(arf3+arc3),2)) AS AR_61_90,
convert(decimal(11,2),round (sum(udf19+udf20),2)) as AR_91_120,
convert(decimal(11,2),round (sum((arf4+arc4)-(udf19+udf20)),2)) as AR_121_180,
convert(decimal(11,2),round (sum(arf5+arc5),2)) as AR_181_365,
convert(decimal(11,2),round (sum(arf6+arc6),2)) as AR_over_365
from df_dwmatter, df_dwcontrol, timekeep, matter, client
where matter=mmatter
and mclient=clnum
and mbillaty=tkinit
and rectype=4020
and pe=defvalue
and mbillaty=@Attorney
group by tkinit, tklast+', ' + tkfirst,
clnum,
clname1,
mmatter,
mdesc1
having sum(arfbal+arcbal)<>0
order by tkinit, tklast+', ' + tkfirst,
clnum,
clname1,
mmatter,
mdesc1
END
``````
0
Question by:VBBRett

LVL 41

Expert Comment

try with ROLLUP

for example:
``````select tkinit as WA_ID, tklast+', ' + tkfirst  as WA_name,
clnum as Client_id,
clname1 as Client_name,
mmatter as Matter_id,
mdesc1 as Matter_name,
convert(decimal(11,2),round (sum(arfbal+arcbal),2)) AS Total_AR,
convert(decimal(11,2),round (sum(arf1+arc1),2)) as AR_0_30,
convert(decimal(11,2),round (sum(arf2+arc2),2)) as AR_31_60,
convert(decimal(11,2),round (sum(arf3+arc3),2)) AS AR_61_90,
convert(decimal(11,2),round (sum(udf19+udf20),2)) as AR_91_120,
convert(decimal(11,2),round (sum((arf4+arc4)-(udf19+udf20)),2)) as AR_121_180,
convert(decimal(11,2),round (sum(arf5+arc5),2)) as AR_181_365,
convert(decimal(11,2),round (sum(arf6+arc6),2)) as AR_over_365
from df_dwtkmat, df_dwcontrol, timekeep, matter, client
where matter=mmatter
and mclient=clnum
and watty=tkinit
and rectype=4020
and pe=defvalue
and watty=@Attorney
group by tkinit, tklast+', ' + tkfirst,
clnum,
clname1,
mmatter,
mdesc1
having sum(arfbal+arcbal)<>0
order by tkinit, tklast+', ' + tkfirst,
clnum,
clname1,
mmatter,
mdesc1
with rollup
``````
0

Author Comment

with rollup gives me the following error in the sql query:

Msg 156, Level 15, State 1, Line 49
Incorrect syntax near the keyword 'END'.
Msg 156, Level 15, State 1, Line 84
Incorrect syntax near the keyword 'END'.

it did not like the with rollup syntax for some reason.
0

Author Comment

with rollup did not give me the intended effect I was looking for.
0

Author Comment

rollup will not work.  I have too many fields and sql only allows 10 fields to be rolled up
0

LVL 41

Accepted Solution

you can use union then

try

``````select tkinit as WA_ID, tklast+', ' + tkfirst  as WA_name,
clnum as Client_id,
clname1 as Client_name,
mmatter as Matter_id,
mdesc1 as Matter_name,
convert(decimal(11,2),round (sum(arfbal+arcbal),2)) AS Total_AR,
convert(decimal(11,2),round (sum(arf1+arc1),2)) as AR_0_30,
convert(decimal(11,2),round (sum(arf2+arc2),2)) as AR_31_60,
convert(decimal(11,2),round (sum(arf3+arc3),2)) AS AR_61_90,
convert(decimal(11,2),round (sum(udf19+udf20),2)) as AR_91_120,
convert(decimal(11,2),round (sum((arf4+arc4)-(udf19+udf20)),2)) as AR_121_180,
convert(decimal(11,2),round (sum(arf5+arc5),2)) as AR_181_365,
convert(decimal(11,2),round (sum(arf6+arc6),2)) as AR_over_365
from df_dwtkmat, df_dwcontrol, timekeep, matter, client
where matter=mmatter
and mclient=clnum
and watty=tkinit
and rectype=4020
and pe=defvalue
and watty=@Attorney
group by tkinit, tklast+', ' + tkfirst,
clnum,
clname1,
mmatter,
mdesc1
having sum(arfbal+arcbal)<>0
union all
select 0 as WA_ID, 'total' as WA_name,
0,
'',
0,
0,
convert(decimal(11,2),round (sum(arfbal+arcbal),2)) AS Total_AR,
convert(decimal(11,2),round (sum(arf1+arc1),2)) as AR_0_30,
convert(decimal(11,2),round (sum(arf2+arc2),2)) as AR_31_60,
convert(decimal(11,2),round (sum(arf3+arc3),2)) AS AR_61_90,
convert(decimal(11,2),round (sum(udf19+udf20),2)) as AR_91_120,
convert(decimal(11,2),round (sum((arf4+arc4)-(udf19+udf20)),2)) as AR_121_180,
convert(decimal(11,2),round (sum(arf5+arc5),2)) as AR_181_365,
convert(decimal(11,2),round (sum(arf6+arc6),2)) as AR_over_365
from df_dwtkmat, df_dwcontrol, timekeep, matter, client
where matter=mmatter
and mclient=clnum
and watty=tkinit
and rectype=4020
and pe=defvalue
and watty=@Attorney
having sum(arfbal+arcbal)<>0
order by tkinit, tklast+', ' + tkfirst,
clnum,
clname1,
mmatter,
mdesc1
``````
0

LVL 142

Assisted Solution

I would also go the UNION method ...
0

## Featured Post

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.