VBBRett
asked on
SQL Queries and adding in a summary record query
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
if the let's say the field of client name is cl_name and i name it in my query as cl_name as client name, which field name do I call, the tag name(client name) or the field name(cl_name)?
you should use the cl_name.. and if you want you may rename with an alias as in
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 '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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm having a little trouble doing it in my stored procedure, please take a look at my stored procedure and tell me where I should put the sql syntax and where
ASKER
Here is the stored procedure. Please tell me how to add the fields marked with the AR tags.
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;ASKER
Can anyone provide me with an update on this particular query please?
from your table
UNION
select 'TOTAL' sum(january), sum(february ), sum(march), 1 as orderby
from your table
order by orderby