Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

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

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

Open in new window

0
VBBRett
Asked:
VBBRett
  • 3
  • 2
2 Solutions
 
ralmadaCommented:
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

Open in new window

0
 
VBBRettAuthor Commented:
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
 
VBBRettAuthor Commented:
with rollup did not give me the intended effect I was looking for.  
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
VBBRettAuthor Commented:
rollup will not work.  I have too many fields and sql only allows 10 fields to be rolled up
0
 
ralmadaCommented:
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

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I would also go the UNION method ...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now