from your table

UNION

select 'TOTAL' sum(january), sum(february ), sum(march), 1 as orderby

from your table

order by orderby

Solved

Posted on 2011-10-18

Is there a way to pull records from a database and at the very bottom doing a summary or adding them all in the last record? for example, I would like to add the values of total at the last record in the query. how do I do that?

let's say there is a guy selling widgets

client name january february march

client1 2 3 3

client2 1 2 4

client3 2 3 4

total 5 8 11

let's say there is a guy selling widgets

client name january february march

client1 2 3 3

client2 1 2 4

client3 2 3 4

total 5 8 11

9 Comments

from your table

UNION

select 'TOTAL' sum(january), sum(february ), sum(march), 1 as orderby

from your table

order by orderby

select client name, january, february , march

from (

select client name, january, february , march, 0 as orderby

from your table

UNION

select 'TOTAL' sum(january), sum(february ), sum(march), 1 as orderby

from your table

) a

order by orderby

select 'client name', january, february , march

from (

select cl_name as 'client name', january, february , march, 0 as orderby

from yourtable

UNION

select 'TOTAL', sum(january), sum(february ), sum(march), 1 as orderby

from yourtable

) a

order by orderby

select cl_name as 'client name', january, february , march

from yourtable

order by cl_name

compute sum(january), sum(february ), sum(march)

```
USE [son_db]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE
[dbo].[SP_ATTORNEY_SUMMARIZE]
@Attorney varchar(5),
@Type varchar(10) --(B) Billing or (W) Working
as
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
COMPUTE
convert(decimal(11,2),round SUM(sum(arfbal+arcbal),2))) as Total_AR,
SUM(convert(decimal(11,2),round (sum(arf1+arc1),2)) as AR_0_30,
SUM(convert(decimal(11,2),round (sum(arf2+arc2),2)) as AR_31_60,
SUM(convert(decimal(11,2),round (sum(udf19+udf20),2)) as AR_91_120,
SUM(convert(decimal(11,2),round (sum((arf4+arc4)-(udf19+udf20)),2)) as AR_121_180,
SUM(convert(decimal(11,2),round (sum(arf6+arc6),2)) as AR_over_365;
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
COMPUTE
SUM(convert(decimal(11,2),round (sum(arfbal+arcbal),2))) as Total_AR,
SUM(convert(decimal(11,2),round (sum(arf1+arc1),2)) as AR_0_30,
SUM(convert(decimal(11,2),round (sum(arf2+arc2),2)) as AR_31_60,
SUM(convert(decimal(11,2),round (sum(udf19+udf20),2)) as AR_91_120,
SUM(convert(decimal(11,2),round (sum((arf4+arc4)-(udf19+udf20)),2)) as AR_121_180,
SUM(convert(decimal(11,2),round (sum(arf6+arc6),2)) as AR_over_365;
```

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Using Case in sql queries | 17 | 50 | |

SQL queries to include Yes or No for the outputresults | 3 | 30 | |

Transactional Replication using SQL Server 2008 | 20 | 30 | |

SQL SERVER Error Message: the backend version is not supported to design database diagrams | 8 | 38 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**20** Experts available now in Live!